Lookup associated value based on minimum date

bwelsher

New Member
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
id1name101/03/2021
id2name201/02/2021
id3name301/04/2021
id4name401/04/2021
id5name501/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)
list1id1id2id3
list2id3id5id1id2id4
list3id1

The formula(s) would return
list1id2name201/02/2021
list2id5name501/01/2021
list3id1name101/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.
Thanks in advance!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
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)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
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,)
 
Solution

bwelsher

New Member
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

bwelsher

New Member
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback.
I'd suggest marking post#23 as the solution
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top