# Lookup associated value based on minimum date

#### bwelsher

##### New Member
Hello all, I have a range of IDs and I need to lookup the name and date associated with each ID then return the id, name and date of the ID with the smallest date.

The source data looks like this
 id1 name1 01/03/2021 id2 name2 01/02/2021 id3 name3 01/04/2021 id4 name4 01/04/2021 id5 name5 01/01/2021 etc..

The data I am looking up from looks like this (the number of different IDs on each list can be up to 100 if that makes a difference to the formula)
 list1 id1 id2 id3 list2 id3 id5 id1 id2 id4 list3 id1

The formula(s) would return
 list1 id2 name2 01/02/2021 list2 id5 name5 01/01/2021 list3 id1 name1 01/03/2021

Note that the same date can be associated in multiple IDs, it would be good to be able to return each different id/name with the minimum date but it is not essential.

Hope that all makes sense, if not let me know.

#### Fluff

##### MrExcel MVP, Moderator
What should happen if you have instances of the same date.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### bwelsher

##### New Member
What should happen if you have instances of the same date.
In that case it is fine to return either of the names and IDs associated with that date

#### Fluff

##### MrExcel MVP, Moderator
ok, try
Excel Formula:
``=INDEX(FILTER(data!\$A\$1:\$B\$70000,(data!\$C\$1:\$C\$70000=D10)*(ISNUMBER(MATCH(data!\$A\$1:\$A\$70000,\$B2:N2,0)))),1,)``

#### bwelsher

##### New Member
ok, try
Excel Formula:
``=INDEX(FILTER(data!\$A\$1:\$B\$70000,(data!\$C\$1:\$C\$70000=D10)*(ISNUMBER(MATCH(data!\$A\$1:\$A\$70000,\$B2:N2,0)))),1,)``
Thanks, that's mostly working but it throws up a calc error when returning the id (the date works fine) for a few of the lists. I can't see anything that might be causing it.

#### Fluff

##### MrExcel MVP, Moderator

That error suggests that the filter cannot find anything that matches the criteria, although I cannot think why.

#### bwelsher

##### New Member
That error suggests that the filter cannot find anything that matches the criteria, although I cannot think why.
My fault! I have two date columns in the source data, one is cleaned and the other is not, it was pointing at the wrong column. All looks good now so thanks very much indeed. Which is the best post to mark as a solution?

#### Fluff

##### MrExcel MVP, Moderator
Glad it's sorted & thanks for the feedback.
I'd suggest marking post#23 as the solution

Replies
4
Views
99
Replies
3
Views
149
Replies
5
Views
96
Replies
4
Views
663
Replies
3
Views
238

1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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