Find max and display cell reference

[mmmdonuts123]

Hello, Need some help with a formula.

I have a bunch of dates in my spreadsheet, Using the max function I want to find the soonest date to now (that's easy) but actually i need the data that's 1 cell to the right of it.
I'm fairy new to excel but have tried paring a max with a offset but no joy.

Alternatively instead of showing the date of max, can the result just show which the address instead?

For example : Say I'm writing this formula in B1 =max(a1,a2,a3)

A1, June 3
A2, June 4
A3, June 5

The soonest is June 5, but the result I want to display in B1 is A3, not June 5.

[TMS]

Maybe:

Please Login or Register  to view this content.

[TMS]

Or (Absolute):

Please Login or Register  to view this content.

Or (Relative):

Please Login or Register  to view this content.

[mmmdonuts123]

Hi TMS , I couldn't make either of those work. I can't upload a picture or workbook for some reason, perhaps because I'm on a work laptop, but I'll try to explain better

A1=Jun-10 B1= 5
A2=Jun-11 B2 = 10
A3=Jun-12 B3= 15
A4=MAX(A1:A3)

My Result at A4 is of course Jun-12, but i want A4 to Display A3 which shows which cell has the correct one. Or the number 15, which has the data I'm actually looking for

[TMS]

Try

Please Login or Register  to view this content.

[TMS]

Note: you need real dates in cells A1:A3.

[TMS]

Thanks for the rep.