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
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)
The formula(s) would return
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!
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!