How to return to the leftmost non zero data

GloriaKo

New Member
Joined
Nov 4, 2011
Messages
5
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Example:

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

Change the range references to suit.
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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?
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710

ADVERTISEMENT

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
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710

ADVERTISEMENT

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

Matty
 

GloriaKo

New Member
Joined
Nov 4, 2011
Messages
5
I've figured out another formula as well........

Array formula:

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

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,773
Members
414,336
Latest member
Nicolas2465

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

Which adblocker are you using?

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
Top