Reference ID, Then Return Nearest Date from Second Reference

[husar]

Hello Excel Community,

The Data:

(A) ID	(B) Test Date	(C) Test Value	(D)	(E) Reference ID	(F) Reference Date	(G) Return Value
10	DD/MM/90	1		10	DD/MM/06	.60
10	DD/MM/05	.60		11	DD/MM/15
10	DD/MM/14	.90		12	DD/MM/08
11	DD/MM/89	.88		13	DD/MM/14
11	DD/MM/06	.90		14	DD/MM/16
11	DD/MM/15	.88		15	DD/MM/90

<tbody>
</tbody>

The Question:
I would like to place a formula in column G. The formula's purpose would be to reference columns E and F and then return the Test Value (C) from the same ID in column A with the closest Test Date to the Reference Date.

For example - Pull the test value for ID 10 that is associated with DD/MM/05, because that is the date closest to the reference date for ID 10 in columns E and F. The return value would be .60.

What I have tried:
Various combinations of INDEX and MATCH together in array formulas. The closest I can get it to do is return the first Test Value from the three available with ID 10. Or, it will return the date in column B closest to column F regardless of the ID the dates are attached to.

Note:
I would eventually like to pull the formula down the row of G so that I can return the appropriate values for all IDs in column E.

Any help would be appreciated. Thank you so much!
Robyn

 

[dazfoz]

Does the attached work if posted in G1?

=INDEX(A$1:F$20,MATCH(1,(A$1:A$20=E1)*(INDEX(B$1:B$20,MATCH(MIN(ABS(B$1:B$20-F1)),ABS(B$1:B$20-F1),0))),-1),3)

 

[husar]

Unfortunately, the formula did not work for me. It returned an incorrect value (.90 vs the correct .60). And, when I dragged it down one cell, it returned "N/A".

Is there another formula that may work? Did it work for you?

 

[husar]

*bump*

I am still looking for a solution if anyone has some ideas.

Thank you!