I am sometimes surprised by consulting the answers given on support sites for Excel, to see what are the answers provided to readers ...
In the example below, you have in Block B2: G8 "1" appearing in various places. The goal is to find in the block J2: L6 identification numbers corresponding to columns in which we find the first "1" then the second, and finally the third.
The proposed solution is to create an intermediate column (column I) in which we group IDs of all the columns containing a "1", thanks to the following formula, which came into I2 and shown to Bottom:
= if (B2 = 1, B $ 1 ;"")& if (C2 = 1; ;"")& C $ 1 if (D2 = 1, D $ 1 if ;"")& (E2 = 1, E $ 1 ;"")& if (F2 = 1, F $ 1 ;"")& if (G2 = 1, G $ 1; ")
Formulas for cells in columns J to L are contained in the screenshot above.
It all seems very complicated ... sadly
I can offer you destroy the column I above and enter the new cell I2, then copying and pasting in I2: K6 formula:
= if (nb ($ B2: $ G2)> column () -9; equiv + H2 (1, OFFSET ($ A2, 0, H2 +1): $ G2, 0 );"")
Note 1 - More need a column I like the middle column of the first solution! In addition, advantage, there is the same formula throughout the block I2: K6!
Note 2 - Note the use function OFFSET () to move the starting point of the vector of searches you have is a very powerful feature!
0 comments:
Post a Comment