Get the latest value for a name or ID

lookup xlookup filter

Return the last matching row's value with XLOOKUP, INDEX/FILTER, or an older-Excel formula.

The problem

A name or ID appears several times and you want the most recent value.

Sample data

Columns: A Invoice, B Client, C Date, D Amount. INV-1 appears twice; the later row has 150. Acme's last amount is 400.

Formulas

XLOOKUP (Excel 2021/365, Sheets):
=XLOOKUP("INV-1",A2:A5,D2:D5,,0,-1)   -> 150

INDEX + FILTER (Excel 2021/365, Sheets):
=INDEX(FILTER(D2:D5,A2:A5="INV-1"),ROWS(FILTER(D2:D5,A2:A5="INV-1")))   -> 150

Older Excel:
=INDEX(D2:D5,MAX(INDEX((B2:B5="Acme")*ROW(B2:B5),0))-ROW(D2)+1)   -> 400

Last row of a contiguous column:
=INDEX(D2:D5,COUNTA(D2:D5))   -> 400

The XLOOKUP and FILTER forms need Excel 2021 or 365; Sheets has both.

Tested on sample data by the Cellwise checker (log CW-PL-20261018-40). AI-made: the text, voice and visuals are generated by AI.

Checked before publishing: a check.