Lookup associated value based on minimum date

B

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!
 
Sort by date Sort by votes
That formula is not doing an exact match, it's checking to see if col A contains those values.
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
For an exact match try
Excel Formula: 
=MIN(FILTER(data!$C$1:$C$70000,ISNUMBER(MATCH(data!$A$1:$A$70000,$B2:N2,0))))
 
Upvote 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
 
Upvote 0
Fluff said:
For an exact match try
Excel Formula: 
=MIN(FILTER(data!$C$1:$C$70000,ISNUMBER(MATCH(data!$A$1:$A$70000,$B2:N2,0))))
Click to expand...

Fluff said:
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
Click to expand...
Do I need to create that named range?
 
Upvote 0
No you don't, it was already in your workbook but is not used in the formula.
 
Upvote 0
Fluff said:
No you don't, it was already in your workbook but is not used in the formula.
Click to expand...
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.
 
Upvote 0
You will need to clear out the cells below & to the right of where you enter the formula.
 
Upvote 0
Fluff said:
You will need to clear out the cells below & to the right of where you enter the formula.
Click to expand...
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
 
Upvote 0
You will need to drag it down to B11 & B12
 
Upvote 0
Fluff said:
You will need to drag it down to B11 & B12
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Match Formula with Datedif
Replies
3
Views
322
AlanY
A
S
  • Solved
Assign a value based on a date within date ranges
Replies
5
Views
652
kevin9999
K
P
  • Solved
Count Dates
Replies
3
Views
325
SanjayGMusafir
SanjayGMusafir
C
  • Solved
Top 10 Value in a date range with multiple criteria
2
Replies
12
Views
616
Fluff
Fluff
R
  • Solved
Formula to search max. value based on criteria in other column
2
Replies
13
Views
682
Fluff
Fluff

Forum statistics

Threads
1,214,827
Messages
6,121,824
Members
449,050
Latest member
Bradel

Share this page

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
Back
Top