Back to All Formulas
Lookup & ReferenceLevel 4: Green (Lookups & Data Models)Difficulty: Intermediate
How to Use XLOOKUP in Excel
The modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.
Formula Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| lookup_value | Required | The value you want to search for. |
| lookup_array | Required | The single row or column where the value is searched. |
| return_array | Required | The range of cells that holds the value you want to retrieve. |
| if_not_found | Optional | Text or value to return if no match is found (avoids #N/A). |
| match_mode | Optional | 0 for exact match (default), -1 for next smaller, 1 for next larger, 2 for wildcard. |
| search_mode | Optional | 1 to search first-to-last, -1 to search last-to-first, 2 for binary search. |
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| EmpID | Employee Name | Department | Salary |
|---|---|---|---|
| E101 | Rahul Sharma | Finance | ₹85,000 |
| E102 | Pooja Verma | Marketing | ₹65,000 |
| E103 | Aman Patel | Engineering | ₹1,20,000 |
Applied Formula:Result: ₹1,20,000
=XLOOKUP("E103", A2:A4, D2:D4, "Not Found")
How it works: Searches for 'E103' in Column A and immediately retrieves the matching salary from Column D.
Common Errors & How to Fix Them
#N/A
Why it happens: The lookup value was not found in the lookup range.
Fix: Provide a friendly fallback using the 4th argument: =XLOOKUP(A2, B:B, C:C, 'Not Found').
#VALUE!
Why it happens: The lookup_array and return_array have different dimensions (e.g. 5 rows vs 6 rows).
Fix: Ensure both ranges span the exact same number of rows or columns.
Ready to Master Excel?
Explore 7 LevelsMaster XLOOKUP in Level 4
Learn XLOOKUP alongside 500+ formulas, hands-on practice workbooks, and verifiable certificates.