Formula guide · Excel and Google Sheets · Updated May 13, 2026
Lookup the Last Match in Excel or Google Sheets
Return the last matching record from a list when the same lookup value appears multiple times.
Quick Answer
Use XLOOKUP with search_mode -1 in Excel to search from bottom to top and return the last match.
Copyable Formula
=XLOOKUP(E2,A2:A100,D2:D100,"Not found",0,-1)Syntax
=XLOOKUP(lookup_value, lookup_range, return_range, , 0, -1)Excel and Google Sheets
Worked Example
| A | D | E |
|---|---|---|
| Client | Status | Lookup client |
| Acme | Open | Acme |
| Acme | Paid | |
| Blue Lab | Open |
Result: Returns Paid because it is the last status for Acme in the list.
Steps
- Use the repeated value as the lookup value.
- Select the column that contains repeated IDs or names.
- Select the return column.
- Set search_mode to -1 so XLOOKUP searches from the bottom.
Common Mistakes
- Using a normal lookup and getting the first match instead of the latest match.
- Sorting the source data without checking which record should be considered latest.
- Forgetting that Google Sheets alternatives may use FILTER, SORT, or LOOKUP patterns.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.