=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) XLOOKUP searches a range or array for a match and returns the corresponding item from a second range. Unlike VLOOKUP, it defaults to exact match, searches left or right, and handles errors natively.
XLOOKUP Function in Excel & Google Sheets
Introduced to replace VLOOKUP, HLOOKUP, and LOOKUP, XLOOKUP is the most powerful and versatile lookup function available in modern spreadsheet software.
1. Basic Exact Match Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])Example Dataset (Employee Directory):
| Emp ID (A) | First Name (B) | Last Name (C) | Department (D) | Salary (E) |
|---|---|---|---|---|
| EMP-101 | James | Miller | Engineering | $115,000 |
| EMP-102 | Rachel | Green | Marketing | $92,000 |
| EMP-103 | Carlos | Mendez | Finance | $108,000 |
| EMP-104 | Sarah | Connor | Operations | $95,000 |
To lookup Salary for EMP-103:
=XLOOKUP("EMP-103", A2:A5, E2:E5, "Employee Not Found")- Returns:
$108,000
2. Returning Multiple Columns at Once (Dynamic Spill)
To return First Name, Last Name, Department, and Salary simultaneously:
=XLOOKUP("EMP-102", A2:A5, B2:E5)- Spills
Rachel,Green,Marketing, and$92,000across 4 adjacent columns in a single formula!
3. Left Lookup (Searching Right-to-Left)
Unlike VLOOKUP which forces the lookup column to be the leftmost column, XLOOKUP can look up in column D and return from column A:
=XLOOKUP("Marketing", D2:D5, A2:A5)- Returns:
EMP-102
4. Reverse Search (Finding the Last Entry)
To find the most recent purchase date or last transaction at the bottom of a ledger, set search_mode to -1:
=XLOOKUP("Widget Pro", A2:A100, B2:B100, "None", 0, -1)5. Two-Way Matrix Lookup (Row and Column Intersection)
Nest two XLOOKUP functions to find the intersection of a specific Product (row) and Month (column):
=XLOOKUP(Target_Product, Product_Col, XLOOKUP(Target_Month, Month_Headers, Data_Matrix))Frequently Asked Questions
Why is XLOOKUP superior to VLOOKUP?
XLOOKUP looks left (can look up columns to the left of the search key), defaults to exact match without needing FALSE, has built-in error handling (if_not_found), and does not break when columns are inserted or deleted.
How do I perform a case-sensitive XLOOKUP?
Wrap the lookup value and array in the EXACT function: =XLOOKUP(TRUE, EXACT(Lookup_Value, Lookup_Array), Return_Array).
Can XLOOKUP return multiple columns at once?
Yes! If return_array spans multiple columns (e.g., C2:E10), XLOOKUP automatically spills all columns horizontally into adjacent cells.