If you’re on Microsoft 365 or Excel 2021+, XLOOKUP is the modern replacement for both VLOOKUP and INDEX/MATCH. It searches in any direction, has a built-in default for missing values, and the syntax is simpler to read. If your organization has upgraded, there’s no reason to use VLOOKUP anymore.
The Formula
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value = what to find | lookup_array = where to search | return_array = what to return | if_not_found = default when no match (optional) | Last two are rarely needed.
The Scenario
Same as every lookup task: you have employee IDs and need to pull their department and salary from an HR database.
HR Database
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Emp ID | Name | Department | Salary |
| 2 | E-101 | Sarah Chen | Engineering | $95,000 |
| 3 | E-105 | James Rivera | Marketing | $72,000 |
| 4 | E-112 | Priya Patel | Finance | $88,000 |
| 5 | E-118 | David Kim | Engineering | $102,000 |
| 6 | E-124 | Lisa Morgan | Operations | $67,000 |
Basic XLOOKUP
Pull the department for employee E-112:
=XLOOKUP("E-112", A:A, C:C)
Search for “E-112” in column A, return the matching value from column C. Result: Finance
Compare that to the VLOOKUP equivalent:
VLOOKUP version
=VLOOKUP("E-112", A:D, 3, FALSE)
You have to count that Department is the 3rd column, and remember to add FALSE. XLOOKUP is more readable.
Built-In Error Handling
With VLOOKUP, you need IFERROR to handle missing values. XLOOKUP has it built in:
=XLOOKUP("E-999", A:A, C:C, "Employee not found")
The fourth argument is the default when no match exists. No IFERROR wrapper needed.
Looking Up Left (No More Limitations)
Need to find an employee’s ID when you only know their name? VLOOKUP can’t do this because the Name column is to the right of Emp ID. XLOOKUP handles it effortlessly:
=XLOOKUP("Priya Patel", B:B, A:A)
Search column B (Name), return from column A (Emp ID). Result: E-112. The search and return columns are independent.
Returning Multiple Columns at Once
XLOOKUP can return multiple columns in a single formula — something neither VLOOKUP nor INDEX/MATCH can do natively:
=XLOOKUP("E-105", A:A, B:D)
Returns Name, Department, AND Salary in one formula across three cells: James Rivera | Marketing | $72,000
Spill behavior
When XLOOKUP returns multiple values, they “spill” into adjacent cells automatically. This is a Microsoft 365 feature called dynamic arrays. Make sure the cells to the right are empty or you’ll get a #SPILL! error.
VLOOKUP vs INDEX/MATCH vs XLOOKUP
| Feature | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| Look up left | No | Yes | Yes |
| Built-in error handling | No (needs IFERROR) | No (needs IFERROR) | Yes (4th argument) |
| Return multiple columns | No | No | Yes (spill) |
| Column insertion safe | No (breaks) | Yes | Yes |
| Readability | Good | Moderate | Best |
| Availability | All versions | All versions | Excel 365 / 2021+ only |
Compatibility note
XLOOKUP only works in Microsoft 365 and Excel 2021 or later. If your workbook needs to be opened by users on older Excel versions (2019 or earlier), they’ll see #NAME? errors. Stick with INDEX/MATCH for maximum compatibility, or confirm all users are on 365 first.
Wildcard and Approximate Matching
XLOOKUP supports wildcard matching with the optional match_mode argument:
=XLOOKUP("E-1*", A:A, B:B, , 2)
The 2 in the match_mode argument enables wildcard matching. Finds the first Emp ID starting with “E-1”.
When Formulas Aren’t Enough
XLOOKUP is the best lookup formula available, but some business scenarios still push beyond what any formula can handle:
- Version compatibility — half your team is on Excel 2019, half on 365, and you need one solution that works everywhere
- Fuzzy matching — “Sarah Chen” vs “S. Chen” vs “Chen, Sarah” won’t match with any lookup formula
- Batch processing — looking up thousands of records across multiple files every week
- Data transformation — the source data needs cleanup, reformatting, or validation before matching
- Audit logging — tracking which records matched, which didn’t, and generating exception reports
A custom VBA lookup tool works across all Excel versions, handles fuzzy matching, processes files in bulk, and generates formatted match reports automatically.
“Fantastic work. Consultative approach and completed within 24 hours.”
Glen F. · PPH
Need Lookups That Work Across Your Entire Organization?
Tell us about your data challenge and we’ll build a solution that works for everyone.
Start a Project