A function which search in the row

[zanshin777]

Is there a function which search in the row (cells on the right of the formula cell) and find non-blank cell?

[Ron Coderre]

What do you want to do with the information?
This formula returns the position of the first non-blank cell within B1:J1

Please Login or Register  to view this content.

This formula returns the value of the first non-blank cell in that range

Please Login or Register  to view this content.

Is that something you can work with?

[Tony Valko]

It should be noted that Ron's formulas only work on TEXT data. Won't work on numeric data.

[Ron Coderre]

Good catch, Biff...Thanks!

These would work for either text or numeric entries and would ignore text blanks....""

Please Login or Register  to view this content.

[AlKey]

This will work with numbers and text

=INDEX(B1:J1,MATCH(TRUE,INDEX((B1:J1<>0),),))

[Tony Valko]

This will work with numbers and text

=INDEX(B1:J1,MATCH(TRUE,INDEX((B1:J1<>0),),))

Just being the nit picker that I am...

What if 0 is a valid entry?

[Ron Coderre]

Slight tweak so that formula won't ignore zeros.

Please Login or Register  to view this content.

(I'm not a fan of omitting the 3rd argument of MATCH of the 2nd/3rd arguments of INDEX...I never remember what the default is :\ )

[AlKey]

Just being the nit picker that I am...

What if 0 is a valid entry?

Well, since I am not totally blind, in case there are zeros, I would use <>""

[Tony Valko]

(I'm not a fan of omitting the 3rd argument of MATCH of the 2nd/3rd arguments of INDEX...I never remember what the default is :\ )

+1

Although, I'm guilty of the same WRT the OFFSET function...

=OFFSET(A1,,,5)

[Ron Coderre]

Well, for that matter, so am I
...but OFFSET doesn't try to match anything one way or another, based on an argument.