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!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
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
Blad1
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
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
Blad1
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
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

ADVERTISEMENT

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
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,197
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, Are you looking for an exact match or a partial match for the value in B2:N2?
 

bwelsher

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

Forum statistics

Threads
1,136,908
Messages
5,678,512
Members
419,768
Latest member
eguechi09x

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