How to return to the leftmost non zero data

[GloriaKo]

I've created a table which is filled in a formular like if(isna(vlookup(XXX)), "", vlookup(XXX)).............Finally I've got a table with either a value or blank.............And now I would like to know how to get the leftmost non blank data......Thanks

 

[Andrew Poulsom]

Example:

=INDEX(B1:E1,MATCH(TRUE,INDEX((B1:E1<>""),)))

Change the range references to suit.

 

[Matty]

Example:

=INDEX(B1:E1,MATCH(TRUE,INDEX((B1:E1<>""),)))

Change the range references to suit

Doesn't it need to be:

=INDEX(B1:E1,MATCH(TRUE,INDEX((B1:E1<>""),)[COLOR=Red],0[/COLOR]))

Or array entered:

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

Matty

 

[Andrew Poulsom]

Doesn't it need to be:

=INDEX(B1:E1,MATCH(TRUE,INDEX((B1:E1<>""),)[COLOR=red],0[/COLOR]))

Or array entered:

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

Matty

My formula worked when I tried it. Didn't it work for you?

 

[Matty]

Hi Andrew,

My formula worked when I tried it. Didn't it work for you?

No, it didn't; your formula returned the right-most non-zero value, not the left-most.

With the match_type omitted, a match_type of 1 is used by MATCH, so it'll pick out the right-most value. This is the expected behaviour. With a match_type of 0, it'll pick out the left-most value.

See example below:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:right; ">5</td><td>
</td><td style="text-align:right; ">7</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">7</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">5</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B3</td><td>=INDEX(B1:E1,MATCH(TRUE,INDEX((B1:E1<>""),)))</td></tr><tr><td>B4</td><td>=INDEX(B1:E1,MATCH(TRUE,INDEX((B1:E1<>""),),0))</td></tr></tbody></table></td></tr></tbody></table>
Strange how you saw something different in your testing.

Matty

 

[Andrew Poulsom]

Strange how you saw something different in your testing.

Probably inadequate test data. I should have known MATCH required a FALSE really.

 

[Matty]

Probably inadequate test data. I should have known MATCH required a FALSE really.

I think we've all done something similar before.

Matty

 

[GloriaKo]

I've figured out another formula as well........

Array formula:

= lookup(9.9999999E+307, if(A3:E3<>"", A3:E3, ""))

 

[barry houdini]

= lookup(9.9999999E+307, if(A3:E3<>"", A3:E3, ""))

That will give you the rightmost number

If you want that then this will be sufficient

=LOOKUP(9.9999999E+307,A3:E3)

 

[GloriaKo]

Yeah.........U are right........thks