🔍 What is XLOOKUP?
XLOOKUP is a powerful Excel function that searches a range or array for a value and returns a corresponding value from another range or array. It is more flexible and modern than older lookup functions like VLOOKUP or HLOOKUP.
🧠 Syntax
XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Description |
| lookup_value | The value you want to search for |
| lookup_array | The range to search in |
| return_array | The range to return the matching value from |
| if_not_found (optional) | What to return if no match is found |
| match_mode (optional) | 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger |
| search_mode (optional) | 1 = first-to-last (default), -1 = last-to-first |
📘 Example
Let’s say you have the following data:
| A | B |
| Name | Age |
| Alice | 25 |
| Bob | 30 |
| Charlie | 28 |
And you want to find the age of “Bob”.
✅ Formula:
=XLOOKUP(“Bob”, A2:A4, B2:B4)
Explanation:
- “Bob” is the value we’re looking for.
- A2:A4 is where we’re looking for “Bob” (the Name column).
- B2:B4 is the range to return the corresponding Age.
👉 Result: 30
💡 With if_not_found
If you want to handle cases where the name isn’t found:
=XLOOKUP(“David”, A2:A4, B2:B4, “Not Found”)
Result: “Not Found” (because “David” isn’t in the list)
✅ Why use XLOOKUP instead of VLOOKUP?
- Works left-to-right and right-to-left
- No need to count columns
- Supports exact match by default
- Better error handling
Let’s walk through a full XLOOKUP example with sample data that you can copy directly into Excel.
📊 Sample Data
| A | B | C |
| Name | Age | Department |
| Alice | 25 | HR |
| Bob | 30 | IT |
| Charlie | 28 | Marketing |
| Diana | 35 | Finance |
✅ Example 1: Find Age of a Person
Suppose you want to find the age of “Charlie”.
Formula:
=XLOOKUP(“Charlie”, A2:A5, B2:B5)
- “Charlie” is what you’re looking for (name).
- A2:A5 is the lookup array (names).
- B2:B5 is the return array (ages).
Result: 28
✅ Example 2: Find Department of a Person
Find the department of “Diana”.
Formula:
=XLOOKUP(“Diana”, A2:A5, C2:C5)
Result: Finance
✅ Example 3: Handle Missing Value
What if you look for someone not in the list? Let’s say “Ethan”.
Formula:
=XLOOKUP(“Ethan”, A2:A5, C2:C5, “Not Found”)
Result: Not Found
✅ Example 4: Lookup Using a Cell Reference
Let’s say cell E1 contains the name you want to look up. Then use:
In E1:
Charlie
In F1 (to find the age):
=XLOOKUP(E1, A2:A5, B2:B5, “Name not found”)
Result: 28
