Need Help with IF Formula

[Mr.India]

Hi All,

I am working on some data need help on the formula.

In the attached sheet, i have given different Scenarios of my data that I may come across (A1:E10). I have tried using If condition and got the result (A15:E23) but i am facing some issue with them and its not matching with the expected result which i want (A26:E34).

Please help me with the formula (i might come across any one of the scenario and it will be changing every week).

[Pepe Le Mokko]

Could you please explain the logic behind this?

[JohnTopley]

Try this in B15

=IFERROR(INDEX(B$2:B$10,SMALL(IF(B$2:B$10<>"",ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($A$1:A1))),"")

Enter with Ctrl+Shift+Enter

Copy across and down

[Mr.India]

Thanks mate, the formula worked perfectly well.

Could you please explain me the logic behind this index and small function.

[JohnTopley]

=IFERROR(INDEX(B$2:B$10,SMALL(IF(B$2:B$10<>"",ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($A$1:A1))),"")

The formula in RED finds all the values in the B range that aren't blank and stores the corresponding numbers in an array. (Hence array formula).

The SMALL function uses the ROWS($A$1:A1) to index through the ROW values i.e finds first, second, third etc row number we have previously stored and thus provides the row number for the INDEX function.

[Czeslaw]

I offer a normal formula.

=IFERROR(INDEX(B$2:B$10,SMALL(INDEX(ROW(A$1:A$9)*(B$2:B$10="")*10^10+ROW(A$1:A$9),0),ROW(A1))),"")