Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Return Matching Data from Most Recent Date

Copper Contributor

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

@Tubolard 

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

@Tubolard 

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! This was the approach I was looking to take but ended up unsuccessful with. Your response works well!
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

@Tubolard 

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

View solution in original post