Excel function guide · Lookup
XLOOKUP function in Excel
XLOOKUP searches one range for a value and returns the item in the same position from another range. It does an exact match by default, can return columns to the left of the lookup column, and has a built-in message for when nothing is found.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XLOOKUP is in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Excel 2019 and earlier don't have it; use INDEX and MATCH there.
| Argument | Required? | What it means |
|---|---|---|
| lookup_value | Required | What you are looking for, usually a cell such as an ID. |
| lookup_array | Required | The single row or column to search. |
| return_array | Required | The row or column to return a value from. Same size as lookup_array. |
| if_not_found | Optional | What to show when there is no match, instead of #N/A. |
| match_mode | Optional | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match. |
| search_mode | Optional | 1 searches first to last (default), -1 last to first; 2 and -2 are binary searches on sorted data. |
XLOOKUP examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee ID | Name | Department | Salary |
| 2 | E-101 | Amira Haddad | Finance | 58,000 |
| 3 | E-102 | Ben Carter | Sales | 46,500 |
| 4 | E-103 | Chen Wei | Sales | 51,200 |
| 5 | E-104 | Dana Novak | HR | 49,800 |
| 6 | E-105 | Elif Kaya | Finance | 63,400 |
1. Return a name from an ID
=XLOOKUP("E-104", A2:A6, B2:B6)Result: Dana Novak
XLOOKUP finds E-104 in the ID column (row 5) and returns the value on the same row of the Name column.
2. Look to the left
=XLOOKUP("Chen Wei", B2:B6, A2:A6)Result: E-103
The return column can be anywhere, including to the left of the column you search. VLOOKUP can't do this.
3. Handle a missing ID
=XLOOKUP("E-110", A2:A6, D2:D6, "Not found")Result: Not found
E-110 isn't in the table. Without the fourth argument the result would be #N/A.
Try it now
Practise XLOOKUP in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In F3, return the name of the employee whose ID is in F2. Point at F2 rather than typing the ID.
Common XLOOKUP mistakes
Ranges of different sizes
lookup_array and return_array must have the same number of rows (A2:A6 with B2:B6, not B2:B7), otherwise XLOOKUP returns #VALUE!.
Expecting an approximate match
XLOOKUP is exact by default. For tiered tables such as commission bands or tax brackets, set match_mode to -1 to get the exact value or the next smaller one.
#N/A from invisible differences
"E-104 " with a trailing space doesn't match "E-104", and the number 1043 doesn't match the text "1043". Clean the data with TRIM, or convert text numbers with VALUE.
Ranges that move when copied
Filling a lookup down shifts A2:A6 to A3:A7 and so on. Lock the table with $A$2:$A$6 so only the lookup value changes.
Sharing with older Excel
In Excel 2019 or earlier the formula shows #NAME?. If colleagues use an older version, write the lookup with INDEX and MATCH.
Practise this in Excel Quest
XLOOKUP Members
Course 6 · Lookups & Relationships · 12 min
Pull matching information from a 30-person employee table by ID — forwards, backwards and safely.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.