Lookup & Reference FormulaGoogle Sheets V8 Engine Verified

Google Sheets XLOOKUP Formula: Modern Search, Syntax & Multi-Column Returns

Q: What is the syntax for the XLOOKUP formula in Google Sheets?

The standard syntax is: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]).

Q: What causes errors in XLOOKUP calculations?

#VALUE! Lookup range and result range sizes do not match: Ensure lookup_range and result_range have the exact same number of rows (e.g. A2:A100 and C2:C100). #N/A Value not found: Provide a missing_value parameter: =XLOOKUP(A2, B:B, C:C, "N/A").

XLOOKUP is the modern, flexible replacement for VLOOKUP and HLOOKUP. It searches left, right, up, and down, defaults to exact match, handles missing data gracefully, and can return entire rows or columns without breaking when columns are inserted.

Formula Syntax:=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Formula Parameters & Arguments

Argument	Type	Required	Description
search_key	Value	Required	The value to search for (e.g. employee ID or email).
lookup_range	Range	Required	The 1D range to search inside (e.g. A2:A100).
result_range	Range	Required	The range to return matching values from (e.g. D2:D100 or D2:F100 for multi-column returns).
missing_value	Value	Optional	Value returned if no match is found (e.g. "Not Found" or 0).
match_mode	Integer	Optional	0 = Exact match (default), -1 = Exact or next smaller, 1 = Exact or next larger, 2 = Wildcard match.
search_mode	Integer	Optional	1 = First-to-last (default), -1 = Last-to-first (reverse search).

Real-World Spreadsheet Examples

Explore verified operational formulas used in enterprise reporting and database workflows:

Example 1: Basic Leftward Exact Match with Missing Fallback

=XLOOKUP(A2, Employees!B:B, Employees!A:A, "Employee ID Not Found")

How it works: Searches employee email in Column B and returns Employee ID from Column A (looking left).

Example 2: Multi-Column Spill Return

=XLOOKUP("SKU-9842", Inventory!A:A, Inventory!C:F, "Out of Stock")

How it works: Finds SKU and spills Product Name, Category, In-Stock Qty, and Price across 4 horizontal cells.

Example 3: Reverse Search for Latest Transaction

=XLOOKUP(A2, Log!A:A, Log!C:C, 0, 0, -1)

How it works: Searches from the bottom of the log table to retrieve the customer’s most recent transaction.

Common Errors & Solutions

❌ #VALUE! Lookup range and result range sizes do not match

Resolution: Ensure lookup_range and result_range have the exact same number of rows (e.g. A2:A100 and C2:C100).

❌ #N/A Value not found

Resolution: Provide a missing_value parameter: =XLOOKUP(A2, B:B, C:C, "N/A").

Performance Pro Tips

XLOOKUP eliminates column index numbers (e.g. `3` in VLOOKUP), so inserting new columns in your database never breaks existing formulas.
Combine XLOOKUP with wildcard matching (`match_mode = 2`) to perform partial substring searches with `*` and `?`.

Frequently Asked Questions

Does XLOOKUP recalculate automatically when data changes?

Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.

Is this formula compatible with Microsoft Excel?

Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.

Related Google Sheets Formulas

Lookup & Reference