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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
That formula is not doing an exact match, it's checking to see if col A contains those values.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
For an exact match try
Excel Formula:
=MIN(FILTER(data!$C$1:$C$70000,ISNUMBER(MATCH(data!$A$1:$A$70000,$B2:N2,0))))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
For cols B & C
excel formula amend.xlsx
ABCDEFGHIJKLMN
1
2list1C1NHR-02395C1NHR-02400C1NHR-02410
3list2C1NHR-02395C1NHR-02400C1NHR-02410C1NHR-024129C1NHR-02430
4list3C1NHR-02395C1NHR-02400C1NHR-02410C1NHR-024129C1NHR-02430C1NHR-02860C1NHR-03500C1NHR-03520C1NHR-03540C1NHR-03550C1NHR-03560F1NHR-00170F1NHR-00190
9
10list1C1NHR-02400build4237614-May-21
11list2C1NHR-024129build4237807-Oct-19
12list3C1NHR-024129build4237807-Oct-19
13
Sheet1
Cell Formulas
RangeFormula
B10:C12B10=FILTER(data!$A$1:$B$70000,(data!$C$1:$C$70000=D10)*(ISNUMBER(MATCH(data!$A$1:$A$70000,$B2:N2,0))))
D10:D12D10=MIN(FILTER(data!$C$1:$C$70000,ISNUMBER(MATCH(data!$A$1:$A$70000,$B2:N2,0))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
data!_FilterDatabase=data!$A$1:$C$66620D10:D12, B10:B12
 

bwelsher

New Member
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows
For an exact match try
Excel Formula:
=MIN(FILTER(data!$C$1:$C$70000,ISNUMBER(MATCH(data!$A$1:$A$70000,$B2:N2,0))))

For cols B & C
excel formula amend.xlsx
ABCDEFGHIJKLMN
1
2list1C1NHR-02395C1NHR-02400C1NHR-02410
3list2C1NHR-02395C1NHR-02400C1NHR-02410C1NHR-024129C1NHR-02430
4list3C1NHR-02395C1NHR-02400C1NHR-02410C1NHR-024129C1NHR-02430C1NHR-02860C1NHR-03500C1NHR-03520C1NHR-03540C1NHR-03550C1NHR-03560F1NHR-00170F1NHR-00190
9
10list1C1NHR-02400build4237614-May-21
11list2C1NHR-024129build4237807-Oct-19
12list3C1NHR-024129build4237807-Oct-19
13
Sheet1
Cell Formulas
RangeFormula
B10:C12B10=FILTER(data!$A$1:$B$70000,(data!$C$1:$C$70000=D10)*(ISNUMBER(MATCH(data!$A$1:$A$70000,$B2:N2,0))))
D10:D12D10=MIN(FILTER(data!$C$1:$C$70000,ISNUMBER(MATCH(data!$A$1:$A$70000,$B2:N2,0))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
data!_FilterDatabase=data!$A$1:$C$66620D10:D12, B10:B12
Do I need to create that named range?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No you don't, it was already in your workbook but is not used in the formula.
 

bwelsher

New Member
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows
No you don't, it was already in your workbook but is not used in the formula.
OK. I'm not too familiar with the FILTER function, it's giving me a #SPILL! error in some cells in column B where it seems to want to occupy the cells imedtediately below.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You will need to clear out the cells below & to the right of where you enter the formula.
 

bwelsher

New Member
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows
You will need to clear out the cells below & to the right of where you enter the formula.
I can see that if I enter the formula into B10 then it will autopopulate C10 but it doesn't seem to autopopulate B11, C11 etc
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
You will need to drag it down to B11 & B12
 

bwelsher

New Member
Joined
May 16, 2019
Messages
13
Office Version
  1. 365
Platform
  1. Windows
You will need to drag it down to B11 & B12
It works for most list if I do that but the occasional list will generate the spill error as in the example below. Could it be something to do with the fact (in this example) that the two lists with the problem both have the same IDs?

Earliest successor IDEarliest successor nameEarliest successor startSuccessors
C1MHHI-00400newbuild114/07/2023C1MHHI-00400
C1MHHI-00100newbuild201/06/2022C1MHHI-00100
C1MHHI-00100newbuild301/06/2022C1MHHI-00100
C1MHHI-00250newbuild403/02/2023C1MHHI-00250
C1MHHI-00390newbuild515/05/2023C1MHHI-00390
C1MHHI-00430newbuild606/09/2023C1MHHI-00430
C1MHHI-00430newbuild706/09/2023C1MHHI-00430
#SPILL!newbuild801/06/2022C1MHHI-00100D1MHHI-00010N10
#SPILL!newbuild901/06/2022D1MHHI-00010N10C1MHHI-00100
C1MHHI-00250newbuild1003/02/2023C1MHHI-00250
C1MHHI-00390newbuild1115/05/2023C1MHHI-00390
C1BHZN-00010newbuild1208/01/2022C1BHZN-00010
C1BHZN-00010newbuild1308/01/2022C1BHZN-00010
C1NHR-01760newbuild1426/11/2021C1NHR-01760
C1NHR-01810newbuild1522/01/2022C1NHR-01810
C1NHR-01860newbuild1610/03/2022C1NHR-01860
C1NHR-01900newbuild1721/04/2022C1NHR-01900
C1NHR-01530newbuild1826/04/2022C1NHR-01530
C1NHR-01570newbuild1902/06/2022C1NHR-01570
C1NHR-00290newbuild2022/02/2025C1NHR-00290
C1NHR-00330newbuild2104/09/2024C1NHR-00300C1NHR-00310C1NHR-00320C1NHR-00330
C1NHR-01640newbuild2209/07/2021C1NHR-01640
 

Forum statistics

Threads
1,141,816
Messages
5,708,753
Members
421,588
Latest member
Wawie

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