Lookup associated value based on minimum date

[bwelsher]

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.
Thanks in advance!

 

[Fluff]

What should happen if you have instances of the same date.

 

[bwelsher]

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]

ok, try

=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]

ok, try

=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]

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

 

[bwelsher]

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]

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