Lookup last non-zero column and return heading

  • 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


    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


    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