Lookup & Reference FormulaGoogle Sheets V8 Engine Verified

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

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])
πŸ’‘ Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

XLOOKUP is the modern, robust successor to legacy lookup formulas (VLOOKUP, HLOOKUP, and LOOKUP). Introduced to eliminate the rigid limitations of index offsets and left-to-right lookup constraints, XLOOKUP searches a lookup array and returns corresponding items from a return array regardless of whether the return column is to the left, right, above, or below the lookup key. Furthermore, it incorporates built-in error handling via its if_not_found parameter, eliminating cumbersome IFERROR wrappers, and defaults to exact matching without requiring an exact match flag.

Formula Comparison: XLOOKUP vs. Alternatives

Compared to VLOOKUP, XLOOKUP never breaks when new columns are inserted into source tables because it references column ranges directly rather than static integer column indices. Compared to INDEX/MATCH, XLOOKUP achieves identical two-way lookup flexibility with simpler, more readable syntax. It also supports reverse searching (bottom-to-top) and wildcard matching (*, ?) natively.

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
search_keyValueRequiredThe value to search for (e.g. employee ID or email).
lookup_rangeRangeRequiredThe 1D range to search inside (e.g. A2:A100).
result_rangeRangeRequiredThe range to return matching values from (e.g. D2:D100 or D2:F100 for multi-column returns).
missing_valueValueOptionalValue returned if no match is found (e.g. "Not Found" or 0).
match_modeIntegerOptional0 = Exact match (default), -1 = Exact or next smaller, 1 = Exact or next larger, 2 = Wildcard match.
search_modeIntegerOptional1 = 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.

Example 4: Left-Hand Lookup with Built-in Missing Value Fallback

=XLOOKUP(E2, D2:D1000, A2:A1000, "Employee ID Not Found", 0)
How it works: Searches Email in Column D and returns Employee Name in Column A (to the left), returning a graceful error string if missing.

Example 5: Reverse Search to Find the Most Recent Status Entry

=XLOOKUP(A2, ActivityLog!A:A, ActivityLog!C:C, "No Records", 0, -1)
How it works: The search_mode parameter -1 scans bottom-to-top, instantly returning the latest logged status update for a customer.

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").
❌ #VALUE! Range dimension mismatch
Resolution: The lookup_range and return_range must share identical row or column dimensions. Ensure both ranges span the same height (e.g., A2:A1000 and D2:D1000).
❌ Lookup failing on numbers formatted as text
Resolution: Ensure data types match. If lookup key is a number but source table stores text, coerce with VALUE(E2) or TO_TEXT(E2).

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: XLOOKUP

Why is XLOOKUP preferred over VLOOKUP in enterprise sheets?

XLOOKUP does not break when columns are added or removed, supports looking up values to the left, provides native if_not_found fallbacks, and executes faster on large datasets.

Can XLOOKUP return multiple columns at once?

Yes. If you specify a multi-column return range like B2:D100, XLOOKUP will dynamically spill all three corresponding columns across adjacent cells.

How does wildcard matching work in XLOOKUP?

Set match_mode to 2. You can then use asterisks (*) for multiple characters or question marks (?) for single characters: =XLOOKUP("*corp*", A2:A100, B2:B100, "None", 2).

Can XLOOKUP perform two-way matrix lookups?

Yes, by nesting two XLOOKUP functions: =XLOOKUP(RowKey, RowRange, XLOOKUP(ColKey, ColRange, TableData)).

Is XLOOKUP available in Excel and Google Sheets simultaneously?

Yes. Both Google Sheets and Microsoft Excel 365 support identical XLOOKUP syntax, ensuring 100% workbook portability.

Related Google Sheets Formulas