Forum Discussion
Return Matching Data from Most Recent Date
Hello,
In my sample workbook I would like to take the serial number from my first worksheet (Column A) and attach the last location from my second worksheet (Column B). I want to be able to check the whole range for matches and return only the location with the most recent last order date.
Worksheet 1
Serial Number | Last Location |
1010 |
|
1020 |
|
1030 |
|
1040 |
|
Work sheet 2
Serial Number | Last Location | Last Order Date |
1010 | A | 01/05/2023 |
1020 | B | 5/20/2023 |
1020 | C | 7/15/2023 |
1030 | A | 1/15/2023 |
1040 | D | 01/05/2023 |
1010 | F | 8/20/2023 |
If I had the correct formula I would return
Serial Number | Last Location |
1010 | F |
1020 | C |
1030 | A |
1040 | D |
I thought I would achieve this with Maxifs or a combination of Filter and Max but I have had no luck. Not sure what the best approach would be here for a larger sample size. Any help would be appreciated, thank you!
In B2 on Worksheet 1:
=FILTER('Worksheet 2'!$B$2:$B$7,('Worksheet 2'!$C$2:$C$7=MAXIFS('Worksheet 2'!$C$2:$C$7,'Worksheet 2'!$A$2:$A$7,A2))*('Worksheet 2'!$A$2:$A$7=A2))
Fill down.
In older versions of Excel, confirmed by pressing Ctrl+Shift+Enter:
=INDEX('Worksheet 2'!$B$2:$B$7, MATCH(1, ('Worksheet 2'!$C$2:$C$7=MAXIFS('Worksheet 2'!$C$2:$C$7, 'Worksheet 2'!$A$2:$A$7, A2))*('Worksheet 2'!$A$2:$A$7=A2), 0))
If your data always has the dates from old to newest at the bottom in sheet 2, you could use XLOOKUP and have it search last-to-first:
=XLOOKUP(A2:A5,SerialNumber,LastLocation,,,-1)This variation is able to handle a situation where the dates in Sheet2 may not be in 'perfect' order.
=LET( sorted, SORT(data, {1, 3}), SN, TAKE(sorted, , 1), LastLoc, INDEX(sorted, , 2), XLOOKUP(A2:A5, SN, LastLoc, , , -1) )- Thank you Patrick, both of your responses would work in this scenario as well!
In B2 on Worksheet 1:
=FILTER('Worksheet 2'!$B$2:$B$7,('Worksheet 2'!$C$2:$C$7=MAXIFS('Worksheet 2'!$C$2:$C$7,'Worksheet 2'!$A$2:$A$7,A2))*('Worksheet 2'!$A$2:$A$7=A2))
Fill down.
In older versions of Excel, confirmed by pressing Ctrl+Shift+Enter:
=INDEX('Worksheet 2'!$B$2:$B$7, MATCH(1, ('Worksheet 2'!$C$2:$C$7=MAXIFS('Worksheet 2'!$C$2:$C$7, 'Worksheet 2'!$A$2:$A$7, A2))*('Worksheet 2'!$A$2:$A$7=A2), 0))
- Thank you! This was the approach I was looking to take but ended up unsuccessful with. Your response works well!
