XLOOKUP finds a value in one range and returns the corresponding value from another range. It replaces VLOOKUP and HLOOKUP with a simpler, more flexible formula that works in any direction.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])What does XLOOKUP do?
XLOOKUP is Excel's modern lookup function. You give it a value to find, the range where that value lives, and the range containing the answer you want. XLOOKUP scans the first range, finds the match, and returns the item in the same position from the second range.
Unlike VLOOKUP, XLOOKUP does not need a column number, does not care whether your return column is to the left or right, and does not break when someone inserts a new column. It also has built-in options for handling missing values and for searching from the bottom up.
Imagine a staff list with names in column A and phone extensions in column B. You type a name in a cell and want the extension to appear next to it. XLOOKUP does that in one formula, and it keeps working as the list grows.
When to use XLOOKUP
- Looking up a product price from a product code
- Finding an employee's department from their name
- Returning a student's grade from their ID
- Pulling a customer's phone number from an order number
- Matching a region to its sales manager
- Finding the most recent entry for a repeated ID
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | Required? | What to enter |
|---|---|---|
lookup_value | Required | The value you want to find. It can be text, a number, a date, or a cell reference. Example: "North" or A2. |
lookup_array | Required | The range or array to search in. Example: A2:A10. |
return_array | Required | The range or array containing the value to return. It should be the same size as lookup_array. Example: B2:B10. |
if_not_found | Optional | What to return when no match is found. Example: "Not found". If omitted, Excel returns #N/A. |
match_mode | Optional | How to match: 0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match. Example: 0. |
search_mode | Optional | Search direction: 1 first to last (default), -1 last to first, 2 binary search ascending, -2 binary search descending. Example: -1. |
What it returns: XLOOKUP returns the value from return_array that sits in the same position as the matched item in lookup_array. The result can be text, a number, a date, or even a whole row or column if return_array is multi-column.
XLOOKUP examples
1 Find a price from a product code
The goal is to get the price for product code P-300. XLOOKUP searches the Product Code column for "P-300", finds it in row 4, then returns the value from the same row in the Price column. The result is 15.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product Code | Price | |||
| 2 | P-100 | 10 | 15 | ||
| 3 | P-200 | 12 | |||
| 4 | P-300 | 15 | |||
| 5 | P-400 | 18 | |||
| 6 |
2 Return a name from an ID
Here the lookup value is an ID number and the answer is a name. XLOOKUP looks for 103 in the ID column and returns the matching name from the Name column. Because the name column is to the left of the ID column, this would be awkward with VLOOKUP but is simple with XLOOKUP. The result is "Maya".
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | ID | |||
| 2 | Sam | 101 | Maya | ||
| 3 | Lena | 102 | |||
| 4 | Maya | 103 | |||
| 5 | Omar | 104 | |||
| 6 |
3 Handle a missing value gracefully
If the product code is not in the list, XLOOKUP would normally return #N/A. By adding the fourth argument, you tell Excel what to show instead. The formula searches for "P-900", finds no match, and returns the text "Not found". This keeps reports clean and readable.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product Code | Price | |||
| 2 | P-100 | 10 | Not found | ||
| 3 | P-200 | 12 | |||
| 4 | P-300 | 15 | |||
| 5 | P-400 | 18 | |||
| 6 |
4 Find a department from an employee name
This is a typical HR lookup. The formula searches the Employee column for "Priya" and returns the value from the Department column in the same row. XLOOKUP matches exact text by default, so capitalisation does not matter. The result is "Finance".
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Department | |||
| 2 | Alex | Sales | Finance | ||
| 3 | Priya | Finance | |||
| 4 | Tom | Support | |||
| 5 | Nina | Marketing | |||
| 6 |
5 Return the latest status for a repeated order
An order can appear more than once as its status changes. By setting search_mode to -1, XLOOKUP searches from the bottom up and returns the last matching status. The formula looks for order "A-102", finds the last occurrence in row 5, and returns "Shipped". This is useful for finding the most recent update.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Status | |||
| 2 | A-101 | Open | Shipped | ||
| 3 | A-102 | Packed | |||
| 4 | A-103 | Open | |||
| 5 | A-102 | Shipped | |||
| 6 | A-104 | Open | |||
| 7 |
Common XLOOKUP errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | The lookup value does not exist in lookup_array and no if_not_found argument was supplied. | Check spelling and data types, or add a fourth argument such as "Not found" to show a friendly message instead. |
| #VALUE! | lookup_array and return_array are not the same size, or an argument has the wrong type. | Make sure both ranges cover the same number of rows (or columns). For example, A2:A10 must pair with B2:B10, not B2:B9. |
| #REF! | A referenced range was deleted or moved, leaving XLOOKUP pointing to an invalid area. | Edit the formula and re-select the ranges, or use structured table references so the ranges update automatically. |
| #SPILL! | XLOOKUP returns multiple values but there is not enough empty space for them to spill. | Clear the cells below or to the right of the formula, or wrap the formula so it returns a single value. |
Tips for using XLOOKUP
- Use XLOOKUP instead of VLOOKUP when you can: it is safer and easier to read.
- Always add the if_not_found argument in reports so missing data shows a clear message rather than #N/A.
- Use
-1as search_mode to get the last match when IDs repeat, such as the latest status update. - Keep lookup_array and return_array the same size to avoid #VALUE! errors.
- Convert your data to an Excel Table and use structured references so XLOOKUP ranges grow with your data.
- Remember that XLOOKUP is not available in Excel 2019 or earlier; use INDEX and MATCH there.
Frequently asked questions
What is the difference between XLOOKUP and VLOOKUP?
XLOOKUP can return values from columns to the left or right, does not need a column number, and has built-in error handling. VLOOKUP only looks to the right and breaks when columns are inserted.
Can XLOOKUP search from the bottom up?
Yes. Set the sixth argument, search_mode, to -1. XLOOKUP then searches from the last row to the first and returns the last match, which is useful for finding the most recent record.
Does XLOOKUP work in older versions of Excel?
XLOOKUP is available in Excel 365, Excel 2021, and Excel for the web. In Excel 2019 and earlier, use INDEX and MATCH as a substitute.
Can XLOOKUP return more than one column?
Yes. If return_array covers several columns, XLOOKUP returns the whole matching row. The result spills into neighbouring cells, so make sure there is enough empty space.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. XLOOKUP reference