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

Excel

Formulas and Functions

HansVogelaar
Jan 10, 2024
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))

HansVogelaar

MVP

Jan 10, 2024

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

Copper Contributor

Jan 10, 2024

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

Forum Discussion

Return Matching Data from Most Recent Date

Share

Resources