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!