Return Most Right Value

[AntNMC1]

Below is a simple table. I would like to know of a formula that will look for the latest date within a row, taking into consideration of matching dates, and return the farthest right header.

Dates	HD1	HD2	HD3	HD4
1st	1/1/2018	1/2/2018	1/2/2018	1/2/2018
2nd	2/5/2018	2/5/2018
3rd	1/15/2018	1/16/2018	1/16/2018
4th	2/26/2018	2/26/2018

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

Thus, column next to HD4 should look like this:
FORMULA
HD4
HD2
HD3
HD2

Thank you!

 

[AntNMC1]

Anybody?

Thanks

 

[Ron Coderre]

With your posted data in A1:E5, this formula returns the header of the last referenced date

F2: =LOOKUP(99^99,B2:E2,$B$1:$E$1)

Copy that formula down through F5

Is that something you can work with?

 

[AntNMC1]

Ron,

Thank you. A follow-up question: what does the 99^99 mean? Out of the formula you provided, I do not how to understand this part.

Thank you

 

[Ron Coderre]

The LOOKUP function has a neat feature (flaw?) that can be exploited.
If your search number is larger than any value in the list....the lookup function returns the LAST numeric value in the list.
As long as the search number is larger than any possible value the correct value will be returned. I used 99^99 because it's so easy to type. Does that help?

 

[Rick Rothstein]

Here is another formula that would work. I am not sure if it would be better than Ron's formula as it involves two function calls instead of one (INDEX is a very fast function though), but given that caveat...

=INDEX(B$1:E$1,,COUNT(B2:E2))

 

[Aladin Akyurek]

Here is another formula that would work. I am not sure if it would be better than Ron's formula as it involves two function calls instead of one (INDEX is a very fast function though), but given that caveat...

=INDEX(B$1:E$1,,COUNT(B2:E2))

In case of an empy cell in-between two numbers, this will flounder and it's never faster than:

=LOOKUP(9.99999999999999E+307,B2:E2,$B$1:$E$1)

when both apply. That big number is there, not because I have insensitive fingers.