SOLVED

# Return Matching Data from Most Recent Date

Copper Contributor

# 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!

5 Replies
best response confirmed by Tubolard (Copper Contributor)
Solution

# Re: Return Matching Data from Most Recent Date

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))

# Re: Return Matching Data from Most Recent Date

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)``

# Re: Return Matching Data from Most Recent Date

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)
)``````

# Re: Return Matching Data from Most Recent Date

Thank you! This was the approach I was looking to take but ended up unsuccessful with. Your response works well!

# Re: Return Matching Data from Most Recent Date

Thank you Patrick, both of your responses would work in this scenario as well!
1 best response

Accepted Solutions
best response confirmed by Tubolard (Copper Contributor)
Solution

# Re: Return Matching Data from Most Recent Date

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))