Return column header based on last value in row

[C.]

Hi all,

Have data that looks like this:

Status 02-10 Status 01-10 Status 12-09 Status 11-09
A 4 2
2
B 7 3
C 8 2 2


Each status is a different column with corresponding values. I want to
be able to find the last value in the row and then return the column
header. For example, for row A, the last value in the row is 2 and I
want to be able to return the column header, "Status 11-09", For Row
B, the last value in the row is 3 and I want to return "Status 12-09"
etc.

Some things to point out: sometimes the numbers are not consecutive
(i.e., a status may be blank) and some statuses may have the same
value (e.g., row A, status 01-10 is the same as status 11-09).

I thought along the lines of Index, hlookup, vlookup, match,
indirect?


Any help would be greatly appreciated.

Thanks!

 

[Gary Brown]

=if(len($E2)<>0,$E$1,if(len($d2)<>0,$d$1,if(len($c2)<>0,$c$1,if(len($b2)<>0,$b$1,""))))

 

[C.]

=if(len($E2)<>0,$E$1,if(len($d2)<>0,$d$1,if(len($c2)<>0,$c$1,if(len($b2)<>0,$b$1,""))))
--
Hope this helps.  
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

Hi Gary,

Thank you so much for your post - I adjusted the formulas as needed
and it worked wonderfully; the only exception is that I forgot to say
that I have 30 columns (starting from Status 10-07 through Status
02-10) which are continually growing each month. I believe when I
tried entering in your formula, there was a cap on the number of IF
statements I could enter. Anyway around this to produce the same
result?

Thanks!

 

[Steve Dunn]

This must be entered as an array formula (i.e. press Ctrl+Shift+Enter rather
than just Enter).

=INDEX(OFFSET($A$1,,,,COUNTA($1:$1)),MAX((OFFSET($A$2,,,,COUNTA($1:$1))<>"")*COLUMN(OFFSET($A$2,,,,COUNTA($1:$1)))))

This will continue to work as your sheet grows, as long as your headings
don't contain any blanks.

=if(len($E2)<>0,$E$1,if(len($d2)<>0,$d$1,if(len($c2)<>0,$c$1,if(len($b2)<>0,$b$1,""))))
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

Hi Gary,

Thank you so much for your post - I adjusted the formulas as needed
and it worked wonderfully; the only exception is that I forgot to say
that I have 30 columns (starting from Status 10-07 through Status
02-10) which are continually growing each month. I believe when I
tried entering in your formula, there was a cap on the number of IF
statements I could enter. Anyway around this to produce the same
result?

Thanks!