Excel Question: Find the column number of a value, and return the value directly below it--how?

I've had a question similar to this one before and I'm not having any luck figuring this one out either. As usual I know what I'm trying to do is ridiculously simple...and yet, I'm managing to not figure it out anyway. (Pretty sure the answer is going to involve some combination of INDEX and MATCH.)

This is what I have: I've got two rows of data, one a list of times and one a list of dates.

So on row 1, column A has 12:00, column B has 12:31, column C has 14:27. And on row 2, column A has 1/1/22, column B has 1/2/22, column C has 1/3/22.

What I want to be able to do is tell Excel to find the maximum value in Row 1 (which I can do with MAX), and then bring back the corresponding value from Row 2, which in this case would be 1/3/22. (This is just dummy data, the real thing extends for several dozen columns and the largest time won't always be the one farthest to the right.)

A lot of the solutions I'm finding seem to be geared towards data laid out vertically instead of horizontally, I'm sure there's a way to adjust formulas to account for that but I've been at this for several hours and I keep getting the dreaded "#REF" in the cell where I'm trying to put the date.


Solution 1:

Try

=HLOOKUP(MAX(A1:D1),A1:D2,2)