Using Index Function to Find 1st non Blank Cell (From the right)

**greyvdub** · 09-17-2010, 10:42 AM

Hi all -

I have data in columns A-X, in a "tree like" hierarchy, so I would like to have a formula in column Y that will find the first non blank cell from A-X and return the result. For Example, row 2 non blank cell may be in Column C and row 3 may be in column X (depending on the depth).

Can this be modified to look up from the Right to the left?

=INDEX(B2:X2,MATCH(TRUE,INDEX((B2:X2<>0),0),0))

many thanks...

**daddylonglegs** · 09-17-2010, 10:47 AM

Try

=LOOKUP(2,1/(B2:X2<>0),B2:X2)

**greyvdub** · 09-17-2010, 10:49 AM

Originally Posted by daddylonglegs

Try

=LOOKUP(2,1/(B2:X2<>0),B2:X2)

Thanks - that looks up well from the right, however returns the first blank cell, rather than going deeper into the rows to find a non-blank cell...

**daddylonglegs** · 09-17-2010, 11:07 AM

Your formula

=INDEX(B2:X2,MATCH(TRUE,INDEX((B2:X2<>0),0),0))

Is specifically looking for the first non-zero cell, which isn't necessarily the same as the first "non-blank" cell

This formula

=LOOKUP(2,1/(B2:X2<>0),B2:X2)

will find the last "non zero" cell in the range. If it returns a blank then perhaps you have formulas in the range that return blanks? If so try changing to

=LOOKUP(2,1/(B2:X2<>""),B2:X2)

....but if the last non blank cell is a zero it will return that value, is that OK? Also your original formula could return a blank if you have "formula blanks" in the range