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!

 

[mart37]

search min date
	A	B	C	D	E	F
1	Id1	name1	1-3-2021
2	id2	name2	1-2-2021
3	id3	name3	1-4-2021
4	id4	name4	1-4-2021
5	id5	name5	1-1-2021
6
7
8	List1	id1	id2	id3
9	list2	id3	id5	id1	id2	id4
10	list3	id1
11
12	List1	id2	name2	1-2-2021
13	list2	id5	name5	1-1-2021
14	list3	Id1	name1	1-3-2021
Blad1

Cell Formulas
Range	Formula
B12:B14	B12	=INDEX($A$1:$A$5,SUMPRODUCT(($C$1:$C$5=D12)*(ISNUMBER(SEARCH($A$1:$A$5,B8:F8)))*ROW($A$1:$A$5)))
C12:C14	C12	=VLOOKUP(B12,$A$1:$B$5,2)
D12:D14	D12	=AGGREGATE(14,6,SEARCH($B8:F8,$A$1:$A$5)*($B8:$F8<>"")*($C$1:$C$5),COUNTA($B8:$F8))

 

[bwelsher]

search min date
	A	B	C	D	E	F
1	Id1	name1	1-3-2021
2	id2	name2	1-2-2021
3	id3	name3	1-4-2021
4	id4	name4	1-4-2021
5	id5	name5	1-1-2021
6
7
8	List1	id1	id2	id3
9	list2	id3	id5	id1	id2	id4
10	list3	id1
11
12	List1	id2	name2	1-2-2021
13	list2	id5	name5	1-1-2021
14	list3	Id1	name1	1-3-2021
Blad1

Cell Formulas
Range	Formula
B12:B14	B12	=INDEX($A$1:$A$5,SUMPRODUCT(($C$1:$C$5=D12)*(ISNUMBER(SEARCH($A$1:$A$5,B8:F8)))*ROW($A$1:$A$5)))
C12:C14	C12	=VLOOKUP(B12,$A$1:$B$5,2)
D12:D14	D12	=AGGREGATE(14,6,SEARCH($B8:F8,$A$1:$A$5)*($B8:$F8<>"")*($C$1:$C$5),COUNTA($B8:$F8))

Thank you very much, this gets me most of the way I think. One issue I have noticed is that if multiple IDs in a list have the same date and are also the earliest date in the list then the id and name formulas generate #REF! errors. Is there any way around this?

 

[mart37]

B12: =INDEX($A$1:$A$5,AGGREGATE(14,6,($C$1:$C$5=D12)*(ISNUMBER(SEARCH($A$1:$A$5,B8:F8))*ROW($A$1:$A$5)),1))

 

[bwelsher]

B12: =INDEX($A$1:$A$5,AGGREGATE(14,6,($C$1:$C$5=D12)*(ISNUMBER(SEARCH($A$1:$A$5,B8:F8))*ROW($A$1:$A$5)),1))

Thanks, working nicely.
I have now tried to point the formulae at the actual source data and I am finding a strange anomaly that I can't work out the cause of. List 3 should report a minimum date of 07-Oct-2019 but instead returns 26-Oct-21. The data tab has thousands of lines so I haven't copied below but if needed it can be viewed on my onedrive

excel formula amend.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	list1	C1NHR-02395	C1NHR-02400	C1NHR-02410
3	list2	C1NHR-02395	C1NHR-02400	C1NHR-02410	C1NHR-024129	C1NHR-02430
4	list3	C1NHR-02395	C1NHR-02400	C1NHR-02410	C1NHR-024129	C1NHR-02430	C1NHR-02860	C1NHR-03500	C1NHR-03520	C1NHR-03540	C1NHR-03550	C1NHR-03560	F1NHR-00170	F1NHR-00190
5
6	activity name	build42375	build42376	build42377	build42378	build42379	build42429	build42568	build42574	build42579	build42581	build42584	build58920	build58923
7	activity date	25-Aug-21	14-May-21	25-Aug-21	07-Oct-19	18-Aug-21	11-Oct-21	02-Feb-22	04-May-22	19-Jan-22	05-Jan-22	25-Feb-22	25-Sep-21	26-Oct-21
8
9
10	list1	C1NHR-02400	build42376	14-May-21
11	list2	C1NHR-024129	build42378	07-Oct-19
12	list3	F1NHR-00190	build58923	26-Oct-21
Sheet1

Cell Formulas
Range	Formula
B6:N6	B6	=INDEX(data!$B:$B,MATCH(B4,data!$A:$A,0))
B7:N7	B7	=INDEX(data!$C:$C,MATCH(B4,data!$A:$A,0))
B10:B12	B10	=INDEX(data!$A$1:$A$70000,AGGREGATE(14,6,(data!$C$1:$C$70000=D10)*(ISNUMBER(SEARCH(data!$A$1:$A$70000,B2:N2))*ROW(data!$A$1:$A$70000)),1))
C10:C12	C10	=INDEX(data!$B$1:$B$66620,MATCH(Sheet1!B10,data!$A$1:$A$66620,0))
D10:D12	D10	=AGGREGATE(14,6,SEARCH($B2:N2,data!$A$1:$A$70000)*($B2:$N2<>"")*(data!$C$1:$C$70000),COUNTA($B2:$N2))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bwelsher]

B12: =INDEX($A$1:$A$5,AGGREGATE(14,6,($C$1:$C$5=D12)*(ISNUMBER(SEARCH($A$1:$A$5,B8:F8))*ROW($A$1:$A$5)),1))

Hi mart37, I don't suppose you had a chance to look into why the formula wasn't returning the minimum value when I pointing it at the real data did you?

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

To get the minimum date try

=AGGREGATE(15,6,data!$C$1:$C$70000/(SEARCH($B2:N2,data!$A$1:$A$70000))/($B2:$N2<>""),1)

 

[bwelsher]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

To get the minimum date try

=AGGREGATE(15,6,data!$C$1:$C$70000/(SEARCH($B2:N2,data!$A$1:$A$70000))/($B2:$N2<>""),1)

I have updated the account details.
Thank you very much, that tweak seems to have done the trick. The formulae calculate very slowly when applied to 100 lists with up to 100 items on them that are being looked up in a data range of 70,000 items but that is not surprising!

 

[Fluff]

Thanks for that, Are you looking for an exact match or a partial match for the value in B2:N2?

 

[bwelsher]

Thanks for that, Are you looking for an exact match or a partial match for the value in B2:N2?

It has to be an exact match, those IDs might only differ by a single character.