XLOOKUP in Google Sheets
Write a safer lookup formula, replace a brittle VLOOKUP, return values from either side, and handle missing matches without wrapping another function around the result.
Reviewed by the SheetsX team
=XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"Not found")How to use XLOOKUP in Google Sheets
Use =XLOOKUP(search_key, lookup_range, result_range, missing_value). For example, =XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"Not found") searches for D2 in A2:A6, returns the value from the same row in B2:B6, and shows Not found when there is no exact match.
Watch SheetsX replace a broken VLOOKUP
In a real Google Sheet, an invalid VLOOKUP returns #REF!. The Agent changes only E2, writes an exact-match XLOOKUP, and verifies the $31.25 price while preserving every other cell.
“Fix the broken price lookup in cell E2. Replace the invalid VLOOKUP with an exact-match XLOOKUP that searches D2 in A2:A6, returns the matching price from B2:B6, and shows "Not found" if there is no match. Keep all source data, formatting, and every other cell unchanged. Do not create a new sheet. Verify the exact formula and result in E2.”
XLOOKUP syntax, argument by argument
The first three arguments do the core work. The last three control a missing result, match behavior, and search direction.
Google's official XLOOKUP reference=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])search_keyThe value or cell to find, such as D2.
lookup_rangeOne row or column containing possible matches.
result_rangeThe aligned row or column containing returned values.
missing_valueOptional text or value to return when nothing matches.
match_mode0 exact, 1 next greater, -1 next lower, or 2 wildcard.
search_mode1 first-to-last, -1 last-to-first, or a binary search mode.
Replace VLOOKUP with XLOOKUP
A VLOOKUP index counts columns inside a table. XLOOKUP names the actual return range, so inserting a column does not silently change which field is returned.
=VLOOKUP(D2,$A$2:$B$6,2,FALSE)=XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"Not found")XLOOKUP vs VLOOKUP in Google Sheets
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Lookup direction | Left, right, vertical, or horizontal | Only returns from columns to the right |
| Return column | Uses a direct result range | Uses a fragile numeric column index |
| Exact match | Exact match is the default | Requires FALSE or 0 explicitly |
| Missing value | Built-in missing_value argument | Usually wrapped in IFNA or IFERROR |
| Inserted columns | Result range remains explicit | Column index can return the wrong field |
Fix common XLOOKUP errors
Start by checking whether the search key and lookup values are truly the same data type, then confirm both ranges have the same length.
- #N/A
The key is missing or mismatched. Add missing_value, trim spaces, and align number-versus-text types.
- #VALUE!
The lookup and result ranges often have different sizes or incompatible one-dimensional shapes.
- Wrong duplicate returned
Default search returns the first match. Use search_mode -1 when the last match should win.
- Approximate result looks wrong
Keep match_mode at 0 for IDs and names; use approximate modes only with deliberate numeric bands.
XLOOKUP formula examples
Return a value from the left
=XLOOKUP(E2,B2:B100,A2:A100)Search column B and return the aligned value from column A.
Return the latest duplicate
=XLOOKUP(D2,A2:A100,B2:B100,"Not found",0,-1)Search from the bottom so the last exact match wins.
Wildcard text match
=XLOOKUP("*"&D2&"*",A2:A100,B2:B100,"Not found",2)Use match mode 2 when the key can appear inside longer text.
Ask the Agent to repair and verify the lookup
Name the broken cell, the lookup and result ranges, the desired missing value, and everything that must remain unchanged. A precise request lets the Agent make a narrow edit and report the exact result.
Install SheetsX- Open a new Agent chat for a clean task context.
- Name the exact cell that contains the broken lookup.
- State the lookup range, result range, and match behavior.
- Protect all source data, formatting, and unrelated cells.
- Ask the Agent to verify both the final formula and value.
XLOOKUP and VLOOKUP questions
Does Google Sheets have XLOOKUP?+
Yes. Google Sheets supports XLOOKUP with the syntax XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode). It can replace many VLOOKUP and HLOOKUP formulas.
What is the XLOOKUP formula in Google Sheets?+
A common exact-match formula is =XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"Not found"). It searches D2 in A2:A6, returns the aligned value from B2:B6, and displays Not found when no match exists.
Is XLOOKUP better than VLOOKUP in Google Sheets?+
XLOOKUP is usually safer because it can look left or right, uses explicit lookup and result ranges, defaults to exact matching, and includes a missing-value argument. VLOOKUP remains useful for familiar, simple tables.
How do I replace VLOOKUP with XLOOKUP?+
Keep the VLOOKUP search key, replace the table range with its first lookup column, and replace the numeric index with the actual result column. For example, =VLOOKUP(D2,A2:B6,2,FALSE) becomes =XLOOKUP(D2,A2:A6,B2:B6).
Why does XLOOKUP return #N/A in Google Sheets?+
The search key may not exist, text may contain hidden spaces, numbers may be stored as text, or the lookup and result ranges may not align. Add a missing_value argument for expected misses, then normalize data types and whitespace.
Can XLOOKUP return values from a column on the left?+
Yes. The result range can be to the left of the lookup range. For example, =XLOOKUP(E2,B2:B100,A2:A100) searches column B and returns the matching value from column A.