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.

Fluff

MrExcel MVP, Moderator
That formula is not doing an exact match, it's checking to see if col A contains those values.

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
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
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
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
No you don't, it was already in your workbook but is not used in the formula.

bwelsher

New Member
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
You will need to clear out the cells below & to the right of where you enter the formula.

bwelsher

New Member
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
You will need to drag it down to B11 & B12

bwelsher

New Member
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 ID Earliest successor name Earliest successor start Successors C1MHHI-00400 newbuild1 14/07/2023 C1MHHI-00400 C1MHHI-00100 newbuild2 01/06/2022 C1MHHI-00100 C1MHHI-00100 newbuild3 01/06/2022 C1MHHI-00100 C1MHHI-00250 newbuild4 03/02/2023 C1MHHI-00250 C1MHHI-00390 newbuild5 15/05/2023 C1MHHI-00390 C1MHHI-00430 newbuild6 06/09/2023 C1MHHI-00430 C1MHHI-00430 newbuild7 06/09/2023 C1MHHI-00430 #SPILL! newbuild8 01/06/2022 C1MHHI-00100 D1MHHI-00010N10 #SPILL! newbuild9 01/06/2022 D1MHHI-00010N10 C1MHHI-00100 C1MHHI-00250 newbuild10 03/02/2023 C1MHHI-00250 C1MHHI-00390 newbuild11 15/05/2023 C1MHHI-00390 C1BHZN-00010 newbuild12 08/01/2022 C1BHZN-00010 C1BHZN-00010 newbuild13 08/01/2022 C1BHZN-00010 C1NHR-01760 newbuild14 26/11/2021 C1NHR-01760 C1NHR-01810 newbuild15 22/01/2022 C1NHR-01810 C1NHR-01860 newbuild16 10/03/2022 C1NHR-01860 C1NHR-01900 newbuild17 21/04/2022 C1NHR-01900 C1NHR-01530 newbuild18 26/04/2022 C1NHR-01530 C1NHR-01570 newbuild19 02/06/2022 C1NHR-01570 C1NHR-00290 newbuild20 22/02/2025 C1NHR-00290 C1NHR-00330 newbuild21 04/09/2024 C1NHR-00300 C1NHR-00310 C1NHR-00320 C1NHR-00330 C1NHR-01640 newbuild22 09/07/2021 C1NHR-01640

Replies
13
Views
263
Replies
3
Views
282
Replies
3
Views
155
Replies
3
Views
247
Replies
1
Views
247

1,181,598
Messages
5,930,800
Members
436,761
Latest member
mintwaxed

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