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.

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mart37

Well-known Member
search min date
ABCDEF
1Id1name11-3-2021
2id2name21-2-2021
3id3name31-4-2021
4id4name41-4-2021
5id5name51-1-2021
6
7
8List1id1id2id3
9list2id3id5id1id2id4
10list3id1
11
12List1id2name21-2-2021
13list2id5name51-1-2021
14list3Id1name11-3-2021
Cell Formulas
RangeFormula
B12:B14B12=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:C14C12=VLOOKUP(B12,\$A\$1:\$B\$5,2)
D12:D14D12=AGGREGATE(14,6,SEARCH(\$B8:F8,\$A\$1:\$A\$5)*(\$B8:\$F8<>"")*(\$C\$1:\$C\$5),COUNTA(\$B8:\$F8))

bwelsher

New Member
search min date
ABCDEF
1Id1name11-3-2021
2id2name21-2-2021
3id3name31-4-2021
4id4name41-4-2021
5id5name51-1-2021
6
7
8List1id1id2id3
9list2id3id5id1id2id4
10list3id1
11
12List1id2name21-2-2021
13list2id5name51-1-2021
14list3Id1name11-3-2021
Cell Formulas
RangeFormula
B12:B14B12=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:C14C12=VLOOKUP(B12,\$A\$1:\$B\$5,2)
D12:D14D12=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

Well-known Member
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

New Member
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
ABCDEFGHIJKLMN
2list1C1NHR-02395C1NHR-02400C1NHR-02410
3list2C1NHR-02395C1NHR-02400C1NHR-02410C1NHR-024129C1NHR-02430
4list3C1NHR-02395C1NHR-02400C1NHR-02410C1NHR-024129C1NHR-02430C1NHR-02860C1NHR-03500C1NHR-03520C1NHR-03540C1NHR-03550C1NHR-03560F1NHR-00170F1NHR-00190
5
6activity namebuild42375build42376build42377build42378build42379build42429build42568build42574build42579build42581build42584build58920build58923
7activity date25-Aug-2114-May-2125-Aug-2107-Oct-1918-Aug-2111-Oct-2102-Feb-2204-May-2219-Jan-2205-Jan-2225-Feb-2225-Sep-2126-Oct-21
8
9
10list1C1NHR-02400build4237614-May-21
11list2C1NHR-024129build4237807-Oct-19
12list3F1NHR-00190build5892326-Oct-21
Sheet1
Cell Formulas
RangeFormula
B6:N6B6=INDEX(data!\$B:\$B,MATCH(B4,data!\$A:\$A,0))
B7:N7B7=INDEX(data!\$C:\$C,MATCH(B4,data!\$A:\$A,0))
B10:B12B10=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:C12C10=INDEX(data!\$B\$1:\$B\$66620,MATCH(Sheet1!B10,data!\$A\$1:\$A\$66620,0))
D10:D12D10=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

New Member
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

MrExcel MVP, Moderator
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
Excel Formula:
``=AGGREGATE(15,6,data!\$C\$1:\$C\$70000/(SEARCH(\$B2:N2,data!\$A\$1:\$A\$70000))/(\$B2:\$N2<>""),1)``

bwelsher

New Member
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
Excel Formula:
``=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

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

bwelsher

New Member
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.

Replies
13
Views
307
Replies
3
Views
291
Replies
3
Views
197
Replies
6
Views
222
Replies
3
Views
278

1,186,796
Messages
5,959,832
Members
438,452
Latest member
Poor Dave

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.

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