Microsoft Excel has changed a lot over the years. The XLOOKUP function is a big improvement. It's more powerful and easier to use than VLOOKUP and HLOOKUP.
In this post, we'll show you how to use the XLOOKUP function step by step. We'll use examples to help you use it in your Excel projects.
What is XLOOKUP?
XLOOKUP helps you find a value in a range or array. It's better than VLOOKUP, HLOOKUP, and some uses of INDEX-MATCH.
Syntax:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Parameters:
- lookup_value: The value you want to search for.
- lookup_array: The range/column where Excel will search.
- return_array: The range/column from which you want to retrieve a value.
- [if_not_found]: (Optional) Value to return if no match is found.
- [match_mode]: (Optional) Control type of match (exact, wildcard, etc.).
- [search_mode]: (Optional) Choose to search top to bottom (default) or bottom to top.
Example Scenario:
Employee Database Example
| Employee ID | Name | Department |
|---|---|---|
| 1001 | John Smith | HR |
| 1002 | Jane Doe | Finance |
| 1003 | Alice Brown | Marketing |
| 1004 | Bob Martin | IT |
Objective:
We aim to find an employee's Department by their Employee ID.
Step-by-Step Guide:
Step 1: Prepare Your Data
Make sure your data is in a table format, like the one above.
Step 2: Write the XLOOKUP Formula
Let's say you want to find the Department of Employee ID 1003.
In an empty cell, type:
=XLOOKUP(1003, A2:A5, C2:C5)
Explanation:
- 1003: The value to look for.
- A2:A5: The range with Employee IDs.
- C2:C5: The range for the department value.
Result:
Marketing
Step 3: Using a Cell Reference for Flexibility
Instead of hardcoding the ID, use a cell reference:
=XLOOKUP(E2, A2:A5, C2:C5)
Where E2 has the Employee ID you're searching for.
This makes lookups more flexible!
Step 4: Adding an “If Not Found” Message
If the Employee ID doesn’t exist, you can customize the result:
=XLOOKUP(E2, A2:A5, C2:C5, "ID Not Found")
So if someone enters an incorrect ID, the formula will return "ID Not Found" instead of an error.
Step 5: Using Wildcards (Optional Advanced)
Let’s say you’re searching for names but only know part of the name:
=XLOOKUP("*Jane*", B2:B5, C2:C5, "No Match", 2)
- *Jane*: The asterisks act as wildcards.
- 2: Enables wildcard match mode.
Result:
Finance
Comparison: XLOOKUP vs VLOOKUP
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Search Direction | Only left-to-right | Can search left-to-right or right-to-left |
| Exact Match Default | Requires extra argument (FALSE) | Exact match is default |
| Handles Missing Data | Returns #N/A | Can customize "not found" message |
| Performance on Large Data | Slower with large datasets | Faster and more efficient |
Summary:
The XLOOKUP function is a game-changer and should be your go-to for most lookup scenarios in Excel. It's simple, flexible, and eliminates the limitations of older functions.
Key Benefits:
- No need to worry about column numbers (unlike VLOOKUP).
- Can search in any direction.
- Built-in error handling.
- Supports wildcards.
Final Example:
Here’s a complete formula you can use:
=XLOOKUP(E2, A2:A5, C2:C5, "ID Not Found", 0)
Use Case:
You're creating an employee search tool where users enter an Employee ID, and the department is returned, with clear error messages if the ID doesn’t exist.
Now it’s your turn!
Try using XLOOKUP in your spreadsheets and let me know in the comments how it improved your workflow.