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!

Return column header based on last value in row
 

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

Return column header based on last value in row
 

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!

Return column header based on last value in row
 

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!