Lookup & Referenceintermediate
XLOOKUP for the last matching value
=XLOOKUP(A2,$F$2:$F$20,$G$2:$G$20,"Not found",0,-1)
Function reference
XLOOKUP in Excel & Google Sheets
XLOOKUP is used in 1 formula pattern in this library. Browse worked examples below, or use the generator to build a custom variation for your specific data.
1 intermediateBuild a custom XLOOKUP formula →
When to use XLOOKUP
- Pull the latest status, most recent price, or last logged owner for a matching key.
Worked examples (1)
Common errors with XLOOKUP
Not found returned unexpectedly
Check whether the lookup values in the source table and lookup cell are stored as the same type, especially text vs numeric IDs.
Frequently asked questions
Why use -1 in XLOOKUP?
The search mode argument of -1 tells XLOOKUP to search from last to first, which returns the last matching row.
Need a custom variation?
Describe your specific XLOOKUP problem and get a formula written for your exact data.
Open generator with XLOOKUP