# Reference ID, Then Return Nearest Date from Second Reference

#### husar

##### New Member
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### dazfoz

##### Board Regular
Does the attached work if posted in G1?

Code:
``=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)``

Last edited:

#### husar

##### New Member
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

##### New Member
*bump*

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

Thank you!

Replies
0
Views
187
Replies
0
Views
1K
Replies
2
Views
224
Replies
0
Views
317
Replies
3
Views
261

1,191,385
Messages
5,986,314
Members
440,017
Latest member
vasanrajeswaran

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back