Part of the free Module 5: Excel Formulas and Functions · Function 102 of 105 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365 and Excel 2021 or later.
The Excel XLOOKUP function searches a range or array for a value and returns the matching item from another range, in any direction. It replaces VLOOKUP, HLOOKUP and most INDEX-MATCH formulas with one simpler function, supports exact, approximate and wildcard matches, and lets you set your own “not found” message instead of #N/A.
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | Required | What it does | Notes and allowed values |
|---|---|---|---|
| lookup_value | Required | The value to search for. | Text, number, date, logical or a cell reference. Can be an array of values in Excel 365, in which case the result spills. |
| lookup_array | Required | The single row or column to search in. | Must be one row or one column. Its size must match return_array in the search direction. |
| return_array | Required | The range or array to return values from. | Can be several columns wide; XLOOKUP then returns the whole row and spills across the neighbouring cells. |
| if_not_found | Optional | Value to show when no match is found. | Any text, number or formula. If omitted, XLOOKUP returns #N/A. |
| match_mode | Optional | How the match is made. | 0 = exact match (default) · -1 = exact or next smaller item · 1 = exact or next larger item · 2 = wildcard match using ?, * and ~ |
| search_mode | Optional | The order of the search. | 1 = first to last (default) · -1 = last to first, useful for the most recent entry · 2 = binary search, lookup_array sorted ascending · -2 = binary search, lookup_array sorted descending |
How XLOOKUP works
XLOOKUP reads lookup_array from top to bottom (or left to right for a row), finds the first cell that satisfies match_mode and returns the item in the same position of return_array. Because the return range is a separate argument, the answer can sit to the left of the lookup column, on another sheet or in a different workbook, and inserting columns between them does not break the formula. Text matching is not case sensitive.
When return_array is wider than one column, or lookup_value is an array, the result is a dynamic array that spills into adjacent empty cells. If those cells are occupied, Excel shows #SPILL!. With match_mode -1 or 1, XLOOKUP does not need sorted data, unlike the approximate match of VLOOKUP; it scans everything and picks the closest value on the requested side.
Version notes. XLOOKUP is available in Excel 365, Excel 2021, Excel 2024 and Excel for the web. It is not available in Excel 2019 or 2016; a file opened there shows #NAME?. Google Sheets added XLOOKUP in 2022 with the same six arguments. On older versions use INDEX with MATCH.
Worked examples
Example 1: exact-match price lookup
A product list holds codes in column A, names in column B and prices in column C. You want the price of the code typed in cell E2.
| A: Code | B: Product | C: Price |
|---|---|---|
| P-101 | Stapler | 4.50 |
| P-102 | Notebook | 2.25 |
| P-103 | Marker set | 6.80 |
| P-104 | Desk lamp | 18.90 |
With P-103 in E2:
=XLOOKUP(E2, A2:A5, C2:C5, "Not found")The result is 6.80. If E2 held P-199 the cell would show Not found instead of #N/A. Note that the lookup column is A and the return column is C; you never count columns as you would in VLOOKUP.
Example 2: look left and return a whole row
Using the same table, you know the product name and want its code. VLOOKUP cannot look to the left; XLOOKUP simply swaps the ranges.
=XLOOKUP("Notebook", B2:B5, A2:A5)Result: P-102. To return every column for a code at once, point return_array at the full table:
=XLOOKUP("P-104", A2:A5, B2:C5)This returns Desk lamp and 18.90 side by side, spilling into two cells.
Example 3: approximate match for a commission band
Commission depends on the sales figure. Bands do not need to be sorted, but here they are for readability.
| A: Sales from | B: Rate | Formula for sales of 7,400 | Result |
|---|---|---|---|
| 0 | 2% | =XLOOKUP(7400, A2:A5, B2:B5, , -1) |
6% |
| 2,500 | 4% | ||
| 5,000 | 6% | ||
| 10,000 | 8% |
match_mode -1 returns the exact value or the next smaller one, so 7,400 falls in the 5,000 band and returns 6%. Use 1 when you need the next larger threshold, for example the first shipping weight that covers a parcel.
Example 4: last match with search_mode -1
A transaction log lists a customer several times and you need the most recent order value. Searching from the bottom finds it without sorting.
| A: Date | B: Customer | C: Amount |
|---|---|---|
| 02-Jun | Acme | 1,200 |
| 09-Jun | Brightline | 640 |
| 15-Jun | Acme | 980 |
| 21-Jun | Acme | 1,450 |
=XLOOKUP("Acme", B2:B5, C2:C5, "None", 0, -1)Result: 1,450, the amount on 21-Jun. The default search_mode 1 would have returned 1,200.
Example 5: two-way lookup with nested XLOOKUP
A sales grid has regions in A2:A4 and months across B1:D1. To find the value for a chosen region and month, use one XLOOKUP to return the whole column and another to pick the row.
| Region | Jan | Feb | Mar |
|---|---|---|---|
| North | 410 | 455 | 390 |
| South | 380 | 402 | 418 |
| West | 295 | 310 | 333 |
=XLOOKUP("South", A2:A4, XLOOKUP("Feb", B1:D1, B2:D4))The inner XLOOKUP returns the Feb column (455, 402, 310); the outer one picks the South row, giving 402. This replaces the classic INDEX-MATCH-MATCH pattern.
XLOOKUP vs VLOOKUP vs INDEX-MATCH
| Feature | XLOOKUP | VLOOKUP | INDEX-MATCH |
|---|---|---|---|
| Look to the left | Yes | No | Yes |
| Default match | Exact | Approximate (must add FALSE) | Approximate in MATCH (must add 0) |
| Built-in not-found text | Yes | No, needs IFERROR | No, needs IFERROR |
| Search from the bottom | Yes | No | No |
| Return several columns | Yes, spills | One column per formula | One column per formula |
| Survives inserted columns | Yes | No | Yes |
| Works in Excel 2019 and earlier | No | Yes | Yes |
Tips and common mistakes
- Leave match_mode out for exact matches. Exact is the default, so most formulas need only the first three arguments.
- Keep lookup_array and return_array the same length. A2:A100 with C2:C99 produces #VALUE!.
- Use if_not_found rather than IFERROR. It is shorter and does not hide genuine errors in the return column.
- Combine with XMATCH for column-position logic. XMATCH shares the same match_mode and search_mode codes.
- Wildcards need match_mode 2. Without it, an asterisk is treated as a literal character.
- Trim your data first. Trailing spaces and text-formatted numbers are the usual reason a value that looks present returns not found. Wrap the lookup in TRIM or VALUE when needed.
- Do not use binary search on unsorted data. search_mode 2 and -2 are faster on huge lists but return wrong answers when the order is not strictly sorted.
Errors and how to fix them
| Error | Cause | Fix |
|---|---|---|
| #N/A | No match found and if_not_found was omitted. | Add an if_not_found value, check for extra spaces or number-versus-text mismatches. |
| #VALUE! | lookup_array and return_array have different sizes, or lookup_array is more than one row or column. | Make both ranges the same height (or width) and keep lookup_array to a single row or column. |
| #NAME? | The workbook is opened in Excel 2019 or earlier, or the function name is misspelt. | Use INDEX and MATCH for older versions; check spelling. |
| #SPILL! | The result needs several cells and one of them is not empty. | Clear the cells below or to the right, or return a single column. |
| #REF! | A referenced sheet or range was deleted. | Repoint the ranges; consider converting the data to a Table so ranges expand automatically. |
Practice exercise
- Copy the product table from Example 1 and write an XLOOKUP that returns the product name (not the price) for a code typed in E2, showing Unknown code when there is no match.
- Return the code and price together in one formula for a product name entered in E3. Expected: two spilled cells.
- Build a tax-band table (0, 12,500, 50,000, 150,000) and return the rate for an income of 62,000 using match_mode -1.
- Using the transaction log from Example 4, return the earliest and the latest Acme amount in two cells by changing only search_mode.
- Rewrite an existing VLOOKUP in your own workbook as XLOOKUP and confirm both give the same answer.
Key takeaways
- XLOOKUP needs three arguments: what to find, where to look and what to return.
- Exact match is the default; approximate matches do not require sorted data.
- if_not_found replaces the IFERROR wrapper most people added to VLOOKUP.
- search_mode -1 returns the last occurrence, ideal for latest-date questions.
- A multi-column return_array spills a whole row, and nested XLOOKUPs make a two-way lookup.
- Available in Excel 365 and 2021 or later only; use INDEX-MATCH for older files.
Related functions and lessons
- Excel Formulas and Functions course hub: every function in this library.
- VLOOKUP: the classic vertical lookup, still needed for Excel 2019 and earlier.
- INDEX and MATCH: the flexible pair XLOOKUP replaces.
- HLOOKUP: horizontal lookup; XLOOKUP handles rows too.
- IFNA: trap #N/A from lookups in older versions.
- Excel Dashboard course: XLOOKUP drives most dashboard selectors.
- Microsoft Support: XLOOKUP function
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
For most tasks, yes. XLOOKUP looks in any direction, defaults to an exact match, has a built-in not-found argument, can search from the bottom and returns several columns at once. VLOOKUP remains useful only when a file must open in Excel 2019 or earlier, where XLOOKUP shows #NAME?.
Why does XLOOKUP return #N/A when the value is there?
Usually the two values are not identical: one has a trailing space, one is a number stored as text, or the lookup is against the wrong column. Wrap the lookup value in TRIM, convert text numbers with VALUE, and confirm the lookup_array is the column containing the value.
Can XLOOKUP return multiple matches?
No. XLOOKUP returns the first (or, with search_mode -1, the last) match only. To list every row that matches a criterion, use the FILTER function, which returns all matching rows as a spilled array. XLOOKUP can, however, return several columns from the one matched row.
Does XLOOKUP work in Excel 2016 or 2019?
No. XLOOKUP was introduced in Excel 365 in 2019 and shipped in the perpetual Excel 2021 release. Excel 2016 and 2019 do not have it. If colleagues use those versions, build the lookup with INDEX and MATCH instead so the workbook calculates correctly everywhere.
How do I use wildcards in XLOOKUP?
Set match_mode to 2. Then a question mark stands for one character and an asterisk for any number of characters, so a lookup value such as Note* finds Notebook. To search for a literal asterisk or question mark, put a tilde in front of it.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems built with these functions are available at NextGenTemplates.com.