# How to return to the leftmost non zero data

#### GloriaKo

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Example:

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

Change the range references to suit.

Example:

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

Change the range references to suit
Doesn't it need to be:

Code:
``=INDEX(B1:E1,MATCH(TRUE,INDEX((B1:E1<>""),)[COLOR=Red],0[/COLOR]))``
Or array entered:

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

Doesn't it need to be:

Code:
``=INDEX(B1:E1,MATCH(TRUE,INDEX((B1:E1<>""),)[COLOR=red],0[/COLOR]))``
Or array entered:

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

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

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

Strange how you saw something different in your testing.

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

Probably inadequate test data. I should have known MATCH required a FALSE really.
I think we've all done something similar before.

Matty

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

Array formula:

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

= 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)

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

Replies
3
Views
305
Replies
3
Views
252
Replies
2
Views
585
Replies
1
Views
191
Replies
3
Views
293

Threads
1,206,756
Messages
6,074,758
Members
446,084
Latest member
WalmitAal

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back