How to Return 1st, 2nd,3rd Number in a Column Containing Blanks?

Please see the attached example. Basically, in a random number list containing blanks, I would like a formula that can return the 1st, 2nd, 3rd, etc, number. Is there a way to do this?

Thanks

Put in M4 and copied down

=IFERROR(INDEX($E$2:$E$19,AGGREGATE(15,6,(ROW($E$2:$E$19)-MIN(ROW($E$2:$E$19))+1)/($E$2:$E$19<>""),ROW(A1))),"")

Another non-array formula option in M4, filling down :

=IFERROR(INDEX(E:E,MATCH(0,MMULT(COUNTIF(M$3:M3,E$2:E$19)+(E$2:E$19=0),1),0)+1),"")

Regards
Bosco

Thanks for all the great answers!

Originally Posted by FlameRetired

@ Bosco.

Nice solution. Would you explain why MMULT?

=IFERROR(INDEX(E:E,MATCH(0,COUNTIF(P$3:P3,E$2:E$19)+(E$2:E$19=0),0)+1),"")

returns the same results.

I.e. COUNTIF(P$3:P3,E$2:E$19)+(E$2:E$19=0)

and

MMULT(COUNTIF(M$3:M3,E$2:E$19)+(E$2:E$19=0),1)

return the same array.

What am I missing?

Hi FlameRetired,

1] Without MMULT, the formula should be array enter

{=IFERROR(INDEX(E:E,MATCH(0,COUNTIF(P$3:P3,E$2:E$19)+(E$2:E$19=0),0)+1),"")}

2] With adding MMULT, the formula become non-array (normal) enter

=IFERROR(INDEX(E:E,MATCH(0,MMULT(COUNTIF(P$3:P3,E$2:E$19)+(E$2:E$19=0),1),0)+1),"")

Regards
Bosco

Hello,

These formulas are array type and if we use functions that are able to work natively with arrays, we no longer use the CTRL + SHIFT + ENTER key combination, but as we say are array functions and their use in a larger range, they slow down the calculation (and recalculation) formulas, even if the CTRL + SHIFT + ENTER combination is not used.