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

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.