# Lookup last non-zero column and return heading

• Please see my attached worksheet. I'm trying to identify the last (rightmost), non-zero value and return the heading of that column. Thanks, Faller.

## Files

• Re: Lookup last non-zero column and return heading

Does this help:

=LOOKUP(1,1/D6:R6,D5:R5)

Wigi

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• Re: Lookup last non-zero column and return heading

Wigi,

Looks like that formula fails if I add 1 to June and 2 to July...the formula returns June, not the last value greater than zero. I need the lookup value in the lookup formula to find the last value, regardless of what that value is....Thanks!!!!

• Re: Lookup last non-zero column and return heading

We can tweak Wigi's formula as follows...

=LOOKUP(2,1/(D6:R6<>0),D5:R5)

Hope this helps!

• Re: Lookup last non-zero column and return heading

Thanks, it did the trick. I appreciate your help. Can't believe such a little formula caused me so much irritation.

• Re: Lookup last non-zero column and return heading

Domenic,

Hate to bother you again, but can you assist me with also making the formula "forward" campatible? Find the first non-zero value from left to right, instead of right to left? Thanks again for your help, I'm not familiar with this lookup function at all. Faller

• Re: Lookup last non-zero column and return heading

Try...

=INDEX(D5:R5,MATCH(TRUE,D6:R6<>0,0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

• Re: Lookup last non-zero column and return heading

Quote from Domenic;282455

We can tweak Wigi's formula as follows...

=LOOKUP(2,1/(D6:R6<>0),D5:R5)

Hope this helps!

Hi!

This is a great formula and helped me meet my requirement.
However, I have no idea how it works. Could you please help me understand how the formula works? For example why do we have the "1/(D6:R6<>0)" as the lookup vector?

• Re: Lookup last non-zero column and return heading

HI rahulkumar88,
click in the cell with the formula then in the "Formula" tool bar click "Evaluate formula"
excel will break the formula down and you will see how it works and step into the evaluation ect...
for further questions it is best to start you're own thread