# 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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### 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
13
Views
263
Replies
3
Views
282
Replies
3
Views
155
Replies
3
Views
247
Replies
1
Views
247

1,181,590
Messages
5,930,778
Members
436,760
Latest member
robgreen43

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