INDEX issue

[cparks]

This is bothering the crap out of me and I'd like to have some help on two issues.

1. In the first table I need help on a formula that will return the 1st, 2nd, 3rd...Nth occurrence of a non-blank cell.

2. The second table I already have an array formula that seems to work with the first cell, but I get a #NUM error on the rest.

Please let me know if I need to explain more. See attached file.

Much thanks.

CP

 

[cparks]

***UPDATE***

I have solved my #2 issue. All I did was instead of at the end of the formula where =(INDEX......ROW(A1), I simply locked it =(INDEX.....ROW($A$1).

If theres's a better way please let me know, but seems to have done the trick.

 

[bosco_yip]

#1 issue, D2 formula copy down :

=IF(B2="","",INDEX(F$2:F$14,COUNT(B$2:B2)))

#2 issue, I2 formula copy down :

=INDEX(G$2:G$14,J2)

Regards

 

[cparks]

Great! Thanks for help on both and good deal on the #2 issue.

On issue#1, instead of assigning them a #, I just dont want any empty cells in between.