• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup the Header based on last value

Juniad

Member
JanFebMarchApril
A2 3
B15
C30
D24

I would like to know the Month name based on last value in a row excluding the zero.
Manual result would be:
A = March
B = April
C = Feb
D = April.
 

vletm

Excel Ninja
Juniad
Do You mean like Your C... ? ... Isn't that show 'Feb' for an answer?
and
... You have written about 'last value'
... ... isn't 'last value' the value, which You've put there the last?
 

Juniad

Member
I mean for C if i put value 4 in Jan column than result will b Jan.. But in that row last value is 3 and result should beFeb.
 

Juniad

Member
Veltm:
this formula is not giving correct answer..
=INDEX(B$1:E$1,1,MATCH(MAX(B2:E2),B2:E2,0))
is is looking max value in row and giving same column header..
Example if i enter max value before the last value in row.. then result will not correct .. because i want the column header based on last value in row ever.
 

Juniad

Member
60022

See in the screen short, in row 5 my last value is 3 in E column.. and answer should be April.. but it is looking max value which is in B5 and giving result Jan
BTW i tried this formula but problem is if there is duplicate value in row then match is giving 1st match result ..
=INDEX($B$1:$E$1,MATCH(LOOKUP(2,1/(B4:E4>0),B4:E4),B4:E4,0))
 

vletm

Excel Ninja
Juniad
Term... terms... terms ... terms...

As I tried to ask with #6 Reply
... which is Your 'last value'?
... ... I didn't get an answer!

... and now, 'last value' is JAN in E-column!
 

vletm

Excel Ninja
Juniad
It seems that You cannot answer to any questions
as well as
It seems that You won't read my replies.
I don't have any more questions.

My previous file gives Your needed results.
 

vletm

Excel Ninja
Juniad
Formulas
1) ... are not on sheet!
2) that my sample need to be .xlsb-format
Test to fill values Yourself, I tested and it seems to give Your needed results.
 
Top