XLOOKUP replaces VLOOKUP on Microsoft 365 and Excel 2021 and later. The syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]), so =XLOOKUP("E-112", A:A, C:C) does what =VLOOKUP("E-112", A:D, 3, FALSE) does with less counting. It searches in any direction, takes a built-in fallback for missing values instead of needing IFERROR, and can return several columns at once.
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 XLOOKUP syntax
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.
Example: HR employee database
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 with a plain range. VLOOKUP and INDEX/MATCH can too on Microsoft 365, but only with array tricks (an array of column numbers, or a column number of 0):
=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 | With an array trick | With an array trick | 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”.
Common questions
Do I have XLOOKUP?
It requires Microsoft 365 or Excel 2021. Type =XLOOK in a cell; if it does not autocomplete, your version does not have it.
What happens if I send the file to someone on an older Excel?
They see #NAME? in every XLOOKUP cell, and the values are lost rather than degraded. If the workbook will be shared beyond your own organization, VLOOKUP or INDEX/MATCH remains the safer choice.
Is XLOOKUP faster than VLOOKUP?
Broadly comparable on ordinary datasets. The gain is readability and fewer mistakes, not speed: no column counting, and a built-in fallback instead of wrapping everything in IFERROR.
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.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.