Lookup Formula/VBA to find correct duplicate

[njones3]

Hi there,

I am looking for a formula/VBA solution that will lookup an ID in a separate list, then, if the ID appears in the list more than once, I want the formula to look at the date in the first list and find the ID in the 2nd list with the closest date match before returning the value in column 2.

The data format is essentially as below:

Table 1

ID (using for lookup)

Date

Return Value

Table 2 (the one i am look up against)

ID (to match table 1)

Return Value (which I need in table 1)

Date

Indicates if ID is a duplicate in the list (Count If formula)

Hopefully that makes sense.

Thanks in advance for any help!