Excel XLOOKUP Mastery — The Evolution of VLOOKUP!
In Excel, commonly used functions for searching and linking data include VLOOKUP, HLOOKUP, INDEX, and MATCH. However, since 2020, a powerful and flexible function called XLOOKUP has been introduced, addressing the shortcomings of its predecessors.
This article walks you through the basics and practical use cases of XLOOKUP!
🔍 What is XLOOKUP?
XLOOKUP is a modern Excel function that allows you to search for values both vertically and horizontally, accurately locating the desired result. It’s available in Microsoft 365 and Excel 2021 or later.
📌 Basic Syntax:
- lookup_value: The value to search for
- lookup_array: The range to search within (e.g., list of IDs)
- return_array: The range to return values from (e.g., list of names)
- if_not_found (optional): Value to display if not found
- match_mode (optional): Matching type (0: exact, -1: less than, 1: greater than, 2: wildcard)
- search_mode (optional): Search direction (1: first-to-last, -1: last-to-first)
🎯 Comparison: XLOOKUP vs VLOOKUP
| Search direction | Right only | Bi-directional |
| Column index required | Yes | No |
| If not found | Error (#N/A) | Custom value |
| Needs sorting | Yes (approximate) | No |
| Array return | Not supported | Supported (multi-dimensional) |
🎨 Color Legend:
- 🔴 Red = Value to look up
- 🟢 Green = Search range
- 🔵 Blue = Return value column
About F4: It locks rows/columns with the $ sign.
- $I3 → Column I is fixed, row 3 can change.
- Copying from J3 to J4 will change $I3 to $I4.
- Copying from I3 to H3 keeps $I3, meaning the column remains locked.
🔁 Searching from Bottom Up
By default, XLOOKUP searches from top to bottom.
To search bottom-up, use:
✅ Multi-condition XLOOKUP
When values like A~D are mixed, and you need multiple criteria, try:
How it works:
- ($D$3:$D$9=I3) → Returns TRUE/FALSE array for code match
- ($C$3:$C$9=H3) → Returns TRUE/FALSE array for gender match
- Multiplying arrays = AND condition (TRUE × TRUE = 1)
- XLOOKUP(1, …) finds the first match where both conditions are true
Note: 1 = TRUE, 0 = FALSE, and + acts like an OR condition
If anything is unclear, feel free to leave a comment!