WRONG RESULTWrong result
Check that every range starts and ends on the same rows, and that numbers are stored as numbers.
LOOKUP & REFERENCE · GOOGLE SHEETS
Verified by JosephineReviewed July 25, 2026✓
XLOOKUP
find a value and return its corresponding result.
Use XLOOKUP to look up without counting columns. This guide shows the exact structure, a working example and the mistakes most likely to break it.
Verified by JosephineReviewed July 25, 2026✓THE FORMULA
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])A
search_keyvalue to find
B
lookup_rangewhere to search
C
result_rangevalues to return
D
missing_valueoptional fallback
TRY IT YOURSELFChange the sheet.
Change the sheet.
Watch the answer update.
Edit any highlighted cell below. The example recalculates immediately.
| A · ID | B · TEAM | C · AMOUNT | |
|---|---|---|---|
| 2 | |||
| 3 | |||
| 4 | |||
| 5 |
RESULT
SalesUpdates liveTry changing a value above. This example runs entirely in your browser.
IN PLAIN ENGLISHHow the formula
How the formula
thinks.
- 01
search_keyvalue to find
- 02
lookup_rangewhere to search
- 03
result_rangevalues to return
- 04
missing_valueoptional fallback
COMMON FIXESWhen XLOOKUP
When XLOOKUP
doesn’t behave.
FORMULA PARSE ERRORFormula parse error
Check commas, quotation marks and closing parentheses. Your locale may use semicolons instead of commas.
UNEXPECTED BLANKSUnexpected blanks
Test the source cells for hidden spaces, empty strings and inconsistent data types.