Excel Formula - Value of the first non blank cell in a row

[grace12]

Hi all, I'm very very new to excel. I came across a formula to get the value of the first non blank cell in a row.

=INDEX(A1:C1, MATCH("*", A1:C1, 0))

This formula works for me, but question is what does "*" mean in excel? Unfortunately the person who posted this formula hasn't responded to me for weeks.

Plz help.

 

[Michael M]

Haven't used that before, but I'm not sure it's correct.....try

=INDEX(A1:C1,MATCH(TRUE,A1:C1<>"",0))

Entered with CTRL + SHIFT + Enter

 

[Robert Mika]

Hi all, I'm very very new to excel. I came across a formula to get the value of the first non blank cell in a row.

=INDEX(A1:C1, MATCH("*", A1:C1, 0))

This formula works for me, but question is what does "*" mean in excel? Unfortunately the person who posted this formula hasn't responded to me for weeks.

Plz help.

"*" is a wildcard .
Is mainly use if you are looking or counting a part of the cell withing range.
Using in this case means "if there is something in a cell" return this cell.
But this will only works for Text values
You can use as well:

For Text:

=LOOKUP("zzzzz",A1:C1)
or for numerical values:
=LOOKUP(9.999999999E+307,A1:C1)
for Numerical values

 

[Aladin Akyurek]

Hi all, I'm very very new to excel. I came across a formula to get the value of the first non blank cell in a row.

=INDEX(A1:C1, MATCH("*", A1:C1, 0))

This formula works for me, but question is what does "*" mean in excel? Unfortunately the person who posted this formula hasn't responded to me for weeks.

Plz help.

"*" means any text value, including a formula blank (i.g., ="") or a text number. Any logical value or number will be ignored, in addition to error values that MATCH itself ignores.

The fact that the match-type is set to 0 (FALSE) lets MATCH pick out the first text value, a formula blank or not.

If you don't want a formula blank as result, see Michael's reply.

An interesting question is how MATCH behaves with "*" as the value to match and with match-type set to 1 (TRUE). The formula

=INDEX(A1:C1, MATCH("*", A1:C1, 1))

returns #N/A, so does...

=LOOKUP("*",A1:C1)

a process consistency in the underlying algorithm, possibly a variant of "binary search." Compare with:

http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html (post #7).

 

[grace12]

Thank you all!!