How can I return the latest value based on a matching ID?

[marktuan]

Hi everyone,
I have a table with multiple entries for the same ID and different dates. I would like to return the most recent value associated with a specific ID.
For example:

ID	Date	Value
A01	01-Jan-2026	100
A01	15-Jan-2026	120
A01	20-Feb-2026	150
B01	05-Jan-2026	80

sports games
If I look up A01, I want Excel to return 150 (the value from the latest date).
What formula would be the best approach for this in Excel 365? Thanks in advance for any help!

 

[shamimarita]

Hello Marktuan,

In Excel 365, a simple and efficient way is to combine XLOOKUP with MAXIFS.

Assuming your data is in A2 (ID, Date, Value) and the lookup ID is in E2, use:
=XLOOKUP(MAXIFS(B:B,A:A,E2),B:B,C:C)

Or, to make sure it returns the latest value for the specific ID only:
=XLOOKUP(
MAXIFS(B:B,A:A,E2),
FILTER(B:B,A:A=E2),
FILTER(C:C,A:A=E2)
)

For ID A01, MAXIFS finds the latest date (20-Feb-2026), and XLOOKUP returns the corresponding value (150).

If your data is stored in an Excel Table, the formula can be written with structured references, which makes it easier to maintain as new records are added.

Hope this helps!

 

[davidbaggins]

Hi everyone,
I have a table with multiple entries for the same ID and different dates. I would like to return the most recent value associated with a specific FNF ID.
For example:

ID	Date	Value
A01	01-Jan-2026	100
A01	15-Jan-2026	120
A01	20-Feb-2026	150
B01	05-Jan-2026	80

If I look up A01, I want Excel to return 150 (the value from the latest date).
What formula would be the best approach for this in Excel 365? Thanks in advance for any help!

If you're using Excel 365, you can do it with a single formula using `XLOOKUP` and `MAXIFS`:

=XLOOKUP(MAXIFS(B:B,A:A,E2),FILTER(B:B,A:A=E2),FILTER(C:C,A:A=E2))

Where:
Column A = ID
Column B = Date
Column C = Value
E2 = the ID you're searching for (e.g., A01)
`MAXIFS` finds the latest date for the selected ID, and XLOOKUP returns the corresponding value.
If your data is in an Excel Table, it's even cleaner with structured references.

 

[Acnologia]

Hi everyone,
I have a table with multiple entries for the same ID and different dates. I would like to return the most recent value associated with a specific ID.
For example:

ID	Date	Value
A01	01-Jan-2026	100
A01	15-Jan-2026	120
A01	20-Feb-2026	150
B01	05-Jan-2026	80

sports games
If I look up A01, I want Excel to return 150 (the value from the latest date).
What formula would be the best approach for this in Excel 365? Thanks in advance for any help!

If you're using Excel 365, you can do it with a single formula using `XLOOKUP` and `MAXIFS`:

=XLOOKUP(MAXIFS(B:B,A:A,E2),FILTER(B:B,A:A=E2),FILTER(C:C,A:A=E2))

Where:
Column A = ID
Column B = Date
Column C = Value
E2 = the ID you're searching for (e.g., A01)
`MAXIFS` finds the latest date for the selected ID, and XLOOKUP returns the corresponding value.
If your data is in an Excel Table, it's even cleaner with structured references.

Hi there!

Since you are using Excel 365, you have access to some powerful dynamic array functions that make this very easy. The best approach depends on whether your data is already sorted by date or not.

Here are the two best solutions for your request, assuming your data is in columns A (ID), B (Date), and C (Value).

Method 1: The​

If your data is not guaranteed to be sorted chronologically, this is the most robust and foolproof method. It filters the data for your specific ID, sorts those results by date in descending order, and then extracts the top value.

Excel

=TAKE(SORT(FILTER(<span>B2</span>:<span>C100</span>, <span>A2</span>:<span>A100</span>=<span>"A01"</span>), <span>1</span>, -<span>1</span>), <span>1</span>, <span>2</span>)<br>
How it works:

• FILTER(B2:C100, A2:A100="A01"): Grabs only the dates and values where the ID is "A01".
• SORT(..., 1, -1): Sorts that filtered array by the 1st column (Date) in descending order (-1), putting the newest date at the top.
• TAKE(..., 1, 2): Takes the 1st row and the 2nd column (the Value) from that sorted list.

Method 2:​

If your data is always added chronologically (meaning the newest dates are always at the bottom of the list), you can use a simple XLOOKUP and tell it to search from the bottom up.

Excel

=XLOOKUP(<span>"A01"</span>, <span>A2</span>:<span>A100</span>, <span>C2</span>:<span>C100</span>, <span>"Not Found"</span>, <span>0</span>, -<span>1</span>)<br>
How it works:

• "A01": The ID you are looking for.
• A2:A100: The column containing the IDs.
• C2:C100: The column containing the Values you want to return.
• "Not Found": What to display if the ID doesn't exist.
• 0: Exact match.
• -1: Search last-to-first. This is the magic step that ensures it grabs the lowest (and therefore newest) entry for that ID.

(Note: You can replace "A01" in either formula with a cell reference, like E1, if you want to create a dynamic lookup field!)