Return column header based on last value in row

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!

=if(len($E2)0,$E$1,if(len($d2)0,$d$1,if(len($c 2)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



"C." wrote:

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!
.

On Apr 9, 2:00*pm, Gary Brown junk_at_kinneson_dot_com wrote:
=if(len($E2)0,$E$1,if(len($d2)0,$d$1,if(len($c 2)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

"C." wrote:
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!
.

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!

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,,,,C OUNTA($1:$1)))))

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



"C." wrote in message
...
On Apr 9, 2:00 pm, Gary Brown junk_at_kinneson_dot_com wrote:
=if(len($E2)0,$E$1,if(len($d2)0,$d$1,if(len($c 2)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

"C." wrote:
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!
.

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!