Lookup function
Excel XLOOKUP function
XLOOKUP searches one range for a value and returns the matching item from another range. It defaults to an exact match, can look left or right, and has a built-in "not found" value. It replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH combos.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Arguments
lookup_value- The value to find.
lookup_array- The column or row to search.
return_array- The column or row to return from. It must be the same size as lookup_array.
if_not_found- What to return if there is no match. Leave it out to get #N/A.
match_mode- 0 for exact (default), -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcards.
search_mode- 1 searches first to last (default), -1 searches last to first.
XLOOKUP examples
Every result below was calculated live from this sample sheet.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Emp ID | Name | Dept | Salary |
| 2 | E-104 | Rosa Diaz | Sales | 62000 |
| 3 | E-117 | Ken Ito | Marketing | 71500 |
| 4 | E-122 | Ava Stone | Sales | 58000 |
| 5 | E-130 | Malik Reed | Finance | 80250 |
Result: Ken Ito
Finds E-117 and returns the name: "Ken Ito".
Result: E-122
Looks left: searches names and returns the ID "E-122".
Result: No match
The fourth argument replaces #N/A with "No match".
Result: Ava Stone
Search mode -1 starts from the bottom, so it returns the last Sales person: "Ava Stone".
How to use XLOOKUP
- Type =XLOOKUP( and the value to find.
- Select the column to search in.
- Select the column to return from.
- Optionally add a "not found" message, then close the bracket.
Common XLOOKUP mistakes
Ranges of different sizes
lookup_array and return_array must be the same height. A2:A5 with B2:B6 gives #VALUE!.
Not available in older Excel
XLOOKUP needs Microsoft 365 or Excel 2021 and later. Shared files opened in Excel 2016 will show #NAME?.
Using binary search on unsorted data
Match modes -1 and 1 work on unsorted data, but search_mode 2 or -2 (binary search) needs sorted data. Leave search_mode at its default unless you need it.
XLOOKUP FAQ
What is the difference between XLOOKUP and VLOOKUP?
XLOOKUP uses separate lookup and return ranges, so it can look left. It also defaults to exact match and has a built-in "not found" argument.
How do I return multiple columns with XLOOKUP?
Make return_array several columns wide, like B2:D5. In Excel 365 the results spill across into neighboring cells.
How do I find the last match with XLOOKUP?
Set search_mode to -1: =XLOOKUP(A2,B:B,C:C,,0,-1). It searches from the bottom up.
Does XLOOKUP work in Excel 2016 or 2019?
No. It is only in Microsoft 365, Excel 2021 and later, and Excel for the web.
Practice XLOOKUP in these lessons
- Lookups: XLOOKUP
- Clean results: Final challenge
- Project: Sales commission report
- Project: Class gradebook
Guides that use XLOOKUP
- How to use INDEX MATCH in Excel
- XLOOKUP with multiple criteria
- How to compare two columns in Excel
- VLOOKUP vs XLOOKUP
- How to return multiple matches in Excel
- How to write a nested IF in Excel
- How to use IFERROR in Excel
- #N/A error in Excel: causes and fixes
Related functions
Updated 2026-10-31