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