Formula to return cell value next to date cell

[southward]

Hi,

I am looking for a formula to find a date in an array and return the cell value from the next column.

I found formula's to find the latest and second to latest date from an array.

=MAX(IF(lookup value = array,array))

and

=LATEST(IF(lookup value = array,array))

I think they need to be modified with OFFSET and/or INDEX to return the cell value next to the date.

I have researched and experimented with many various combination without success.

Attached is an example spreadsheet.

Hope this makes sense and someone can help.

Thanks

[AlKey]

Enter formula in L2 and copy down to cell L8

Please Login or Register  to view this content.

[southward]

Hi AlKey,

Thanks for the reply.
It isn't exactly what I was looking for. I realize I neglected to indicate that there are similar dates across the list of names.
I didn't make the dates similar in my example.

However your suggestion reminded me of a similar formula.

I came up with this:

Please Login or Register  to view this content.

I think I couldn't figure it out before, due to thinking I had to use OFFSET.

Thanks for the push in the right direction