Master XLOOKUP: The Ultimate Guide
1. Introduction to XLOOKUP
XLOOKUP is Excel's modern replacement for VLOOKUP, HLOOKUP, and INDEX/MATCH combinations. Available in Excel 365 and Excel 2021, this powerful function simplifies data lookups while providing more flexibility and better performance.
Key Benefits:
- Works in any direction (left, right, up, down)
- Returns multiple values with a single formula
- Handles missing values gracefully
- No more counting columns!
2. XLOOKUP Syntax Explained
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Parameters:
- lookup_value
- The value you want to find
- lookup_array
- The range where Excel searches for the lookup_value
- return_array
- The range containing the values to return
- if_not_found (optional)
- Value to return if no match is found (default: #N/A)
- match_mode (optional)
- 0: Exact match (default)
- -1: Exact match or next smallest
- 1: Exact match or next largest
- 2: Wildcard match
- search_mode (optional)
- 1: Search first to last (default)
- -1: Search last to first
- 2: Binary search (ascending)
- -2: Binary search (descending)
3. Practical Examples
Example 1: Basic Product Lookup
Find the price of a product from a price list:
=XLOOKUP("Apple", A2:A10, B2:B10)This searches for "Apple" in column A and returns the corresponding price from column B.
Example 2: Employee Information Lookup
Find employee department with custom error message:
=XLOOKUP(E2, A2:A100, C2:C100, "Employee not found")Returns the department from column C, or "Employee not found" if the ID doesn't exist.
Example 3: Two-way Lookup
Find sales data for specific product and month:
=XLOOKUP(Product, A2:A50, XLOOKUP(Month, B1:M1, B2:M50))Nested XLOOKUP for row and column lookup - much simpler than INDEX/MATCH!
Try It: Experience XLOOKUP Power
Practice using XLOOKUP to see how it simplifies lookup operations compared to VLOOKUP.
Instructions:
- Employee data is in columns A-D (ID, Name, Department, Salary)
- Enter an employee ID in cell F2 (try 103)
- XLOOKUP Examples:
- In G2:
=XLOOKUP(F2,A2:A6,B2:B6)- Get employee name - In H2:
=XLOOKUP(F2,A2:A6,C2:C6)- Get department - In I2:
=XLOOKUP(F2,A2:A6,D2:D6)- Get salary - Or try in G2:
=XLOOKUP(F2,A2:A6,B2:D6)- Get all three at once! - XLOOKUP with error handling:
- Try:
=XLOOKUP(F2,A2:A6,B2:B6,"Not Found") - Then change F2 to 999 to see the custom error message
💡 XLOOKUP Advantages:
- No need to count columns - just select the return range
- Can return multiple values with one formula
- Built-in error handling with custom messages
- Works in any direction (left, right, up, down)
Formula Comparison
XLOOKUP (Modern)
=XLOOKUP(A2, B:B, D:D)Simple, flexible, powerful
| ID | Name | Dept | Salary |
|---|---|---|---|
| 101 | John | Sales | $50K |
| 102 | Jane | IT | $60K |
VLOOKUP (Traditional)
=VLOOKUP(A2, B:E, 4, FALSE)Need to count columns, only looks right
| ID | Name | Dept | Salary |
|---|---|---|---|
| 101 | John | Sales | $50K |
| 102 | Jane | IT | $60K |
4. XLOOKUP vs VLOOKUP
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Lookup direction | Any direction | Only right |
| Column counting | Not required | Required |
| Default if not found | Customizable | #N/A only |
| Multiple values | Yes | No |
| Performance | Faster | Slower |
5. Advanced Techniques
Single Formula, Multiple Results
Formula: =XLOOKUP("101", A:A, B:D)Returns entire row from columns B, C, D
| A: ID | B: Name | C: Department | D: Salary | E: Location |
|---|---|---|---|---|
| 101 ✓ | John Smith | Sales | $55,000 | NYC |
| 102 | Jane Doe | Marketing | $62,000 | LA |
| 103 | Bob Wilson | IT | $70,000 | Chicago |
Array Result:
All three values returned with one formula!
Return Multiple Columns
Get name, department, and salary with one formula:
Formula:
=XLOOKUP(A2, EmployeeID, EmployeeData)How it works:
- A2: Contains the employee ID to look up
- EmployeeID: Named range or column with employee IDs
- EmployeeData: Named range spanning multiple columns (B:D)
Result:
When EmployeeData spans columns B:D, this single formula returns all three values:
Wildcard Pattern Matching
Visual Example
Formula: =XLOOKUP("*Pro*", A:A, B:B, "Not found", 2)Finds any product containing "Pro" (match_mode = 2)
| Product Name | Price |
|---|---|
| Surface Laptop | $999 |
| MacBook Pro ✓ | $1,299 |
| iPad Air | $599 |
| Surface Pro | $899 |
Wildcard Patterns:
"*Pro*" → Contains "Pro""Mac*" → Starts with "Mac""*book" → Ends with "book""???-???" → 3 chars, dash, 3 charsWildcard Searches
Find partial matches using wildcards:
Formula:
=XLOOKUP("*" & SearchTerm & "*", ProductNames, Prices, "Not found", 2)Parameters:
- "*" & SearchTerm & "*": Wraps search term with wildcards
- ProductNames: Range to search in
- Prices: Range to return values from
- "Not found": Custom error message
- 2: match_mode for wildcard matching
Wildcard Characters:
* = Any number of characters? = Single character*Pro* = Contains "Pro"A?? = Starts with "A" + 2 charsLast Occurrence
Find the most recent entry:
Formula:
=XLOOKUP(CustomerID, A:A, B:B, "No orders", 0, -1)Parameters:
- CustomerID: Value to look up
- A:A: Column to search in
- B:B: Column to return values from
- "No orders": Custom error message
- 0: match_mode for exact match
- -1: search_mode for bottom-to-top search
Use Cases:
6. Common Errors & Solutions
#NAME? Error
Cause: Excel doesn't recognize XLOOKUP (older version)
Solution: Upgrade to Excel 365 or Excel 2021, or use VLOOKUP/INDEX-MATCH
#VALUE! Error
Cause: Arrays have different sizes
Solution: Ensure lookup_array and return_array have the same number of rows/columns
#SPILL! Error
Cause: Result array is blocked by existing data
Solution: Clear cells where the formula needs to spill results
Practice Exercise
Ready to master XLOOKUP? Try this exercise:
- Create a product list with columns: Product ID, Name, Category, Price
- Use XLOOKUP to find a product's price by name
- Create a formula that returns both category and price with one XLOOKUP
- Add error handling for products that don't exist
Ready for the Next Step?
Continue your Excel journey with: Pivot Tables Mastery