A lookup range that stops short
=XLOOKUP(A2,F2:F5,H2:H5)
The checker says: Your range stops at F5. The data runs to F6.
It works for ST-3302, but a code on row 6 would come back as #N/A. Cover the whole list.
Excel function
XLOOKUP in Excel
XLOOKUP finds a value in one column and returns the value from the same row of another. It looks for an exact match by default, and the column it returns can sit on either side.
XLOOKUP is in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Excel 2019 and earlier don’t have it, so use VLOOKUP or INDEX and MATCH there.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | What it does |
|---|---|
| lookup_value | What to look for, such as a product code. |
| lookup_array | The column (or row) to search. |
| return_array | The column to return from. It needs the same number of rows as lookup_array. |
| if_not_found | Optional. What to show when there’s no match. Without it, XLOOKUP returns #N/A. |
| match_mode | Optional. 0 for an exact match (the default). -1 and 1 also accept the next smaller or next larger item. 2 allows wildcards. |
| search_mode | Optional. 1 searches from first to last (the default), -1 from last to first. |
Here’s what that formula is doing.
=XLOOKUP(A2,F2:F6,H2:H6) looks for the code in A2, ST-3302, in the code column F2:F6. It finds it on row 4 and returns 9.99 from the price column, H2:H6.
Try it with real data.
Type a formula, then check it. Hints step in when you’re stuck, and the last one shows the answer.
Example data
Task 1
In C2, look up the price for the code in A2.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Code | Qty | Price | Code | Product | Price | ||
| 2 | ST-3302 | 4 | (answer cell) | NB-2041 | Notebook | 3.50 | ||
| 3 | NB-2041 | 10 | PN-1180 | Pens | 4.20 | |||
| 4 | FD-1005 | 30 | ST-3302 | Stapler | 9.99 | |||
| 5 | DL-4410 | 1 | DL-4410 | Desk lamp | 24.00 | |||
| 6 | FD-1005 | Folders | 1.80 |
Scroll the sheet sideways to see every column.
Nice. Try the next task
Example data
Task 2
B1 holds an ID that isn't on the list. In B2, return the name, or Not found when there's no match.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Staff ID | S107 | ID | Name | Department | |
| 2 | Name | (answer cell) | S101 | Ola Mensah | Sales | |
| 3 | S102 | Priya Nair | Finance | |||
| 4 | S103 | Tom Becker | Sales | |||
| 5 | S104 | Lerato Dube | Operations | |||
| 6 | S105 | Marco Rossi | Finance | |||
| 7 | S106 | Aiko Sato | Operations |
Scroll the sheet sideways to see every column.
That’s both. Practise the whole course free.
Common mistakes, and what our checker says.
Each message below is the checker’s real reply to that formula on the example data.
Ranges of different sizes
=XLOOKUP(A2,F2:F6,H2:H5)
The checker says: The range XLOOKUP searches and the range it returns from need the same number of rows. Check where each one ends.
Excel returns #VALUE! when the two ranges don’t line up. Start and end them on the same rows.
Search and return ranges swapped
=XLOOKUP(A2,H2:H6,F2:F6)
The checker says: The ranges are the wrong way round. XLOOKUP searches the second argument and returns from the third.
The second argument is where to look. The third is what to bring back.
No plan for a missing match
=XLOOKUP(B1,D2:D7,E2:E7)
The checker says: S107 isn't on the list, so the lookup shows #N/A. Add a fourth argument to XLOOKUP: "Not found".
The fourth argument says what to show instead of #N/A.
Questions
Is XLOOKUP better than VLOOKUP?
For most lookups, yes. XLOOKUP matches exactly by default, can return a column to the left, and keeps working when someone inserts a column. VLOOKUP is still worth knowing, because older workbooks use it.
Is XLOOKUP case-sensitive?
No. It treats st-3302 and ST-3302 as the same code.
Can XLOOKUP return more than one column?
Yes. Give it a return range several columns wide, such as G2:H6, and the results spill into the cells to the right.
XLOOKUP is part of module 8 of Excel Foundations: Look things up.
Start practising now.
Excel Foundations is free, with no sign-up. Your progress is saved in this browser.