Forum Discussion
Solved
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...
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))
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!