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.
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])Technical Overview & Engine Mechanics
Formula Comparison: XLOOKUP vs. Alternatives
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")Example 2: Multi-Column Spill Return
=XLOOKUP("SKU-9842", Inventory!A:A, Inventory!C:F, "Out of Stock")Example 3: Reverse Search for Latest Transaction
=XLOOKUP(A2, Log!A:A, Log!C:C, 0, 0, -1)Example 4: Left-Hand Lookup with Built-in Missing Value Fallback
=XLOOKUP(E2, D2:D1000, A2:A1000, "Employee ID Not Found", 0)Example 5: Reverse Search to Find the Most Recent Status Entry
=XLOOKUP(A2, ActivityLog!A:A, ActivityLog!C:C, "No Records", 0, -1)Common Errors & Solutions
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
IMPORTRANGE
IMPORTRANGE imports a specified cell range from another external Google Spreadsheet workbook. It provides real-time multi-sheet linking with built-in OAuth access permission controls.
Lookup & ReferenceINDEX / MATCH
Combining INDEX with MATCH provides two-way horizontal and vertical matrix lookups. It looks left and right, supports dynamic column and row coordinates, and is resilient against structural spreadsheet changes.
Lookup & ReferenceINDIRECT
INDIRECT converts a text string into an active cell or range reference. Enables dynamic switching between sheet tabs (e.g. querying Jan, Feb, Mar depending on a dropdown selection) without re-writing formulas.