# XLOOKUP function in Excel

> XLOOKUP searches one column or row for a value and returns the matching item from another.

Source: https://spreadsheetpractice.com/functions/xlookup · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** XLOOKUP searches one column or row for a value and returns the matching item from another.
- **Syntax:** `=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`
- **Most common use:** `=XLOOKUP(E2,A2:A5,C2:C5)` returns $54 (Price for the SKU typed in E2)
- **Available in:** Excel 2021, 2024 and Microsoft 365
- **Category:** [Lookup functions](https://spreadsheetpractice.com/functions/category/lookup)
- **Returns:** The matching value. If return_array is several columns wide, the whole matching row spills to the right.
- **Google Sheets:** Available in Google Sheets with the same arguments.

## What it does

XLOOKUP searches one column or row for a value and returns the matching item from another. It replaces VLOOKUP, HLOOKUP and most INDEX/MATCH combinations: it can look left, it uses an exact match unless you say otherwise, and it has a built-in fallback for values it can't find.

## Syntax

`=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`

| Argument | Required? | What it is |
|---|---|---|
| `lookup_value` | Required | The value to find, usually a cell reference such as an ID. |
| `lookup_array` | Required | The single column or row to search. |
| `return_array` | Required | The column or row to return from. It must be the same height (or width) as lookup_array. |
| `if_not_found` | Optional | What to return when there is no match, instead of #N/A. |
| `match_mode` | Optional | 0 exact match (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. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | `=XLOOKUP(E2,A2:A5,C2:C5)` | $54 | Price for the SKU typed in E2 |
| F3 | `=XLOOKUP("Monitor arm",B2:B5,A2:A5)` | B-210 | Looks left: the SKU for a product name |
| F4 | `=XLOOKUP("Z-999",A2:A5,C2:C5,"Not found")` | Not found | A missing SKU returns the fallback text instead of #N/A |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | SKU | Product | Price |  | SKU to find | Result |
| 2 | A-100 | Desk lamp | $39 |  | C-330 | $54 |
| 3 | B-210 | Monitor arm | $89 |  |  | B-210 |
| 4 | C-330 | Laptop stand | $54 |  |  | Not found |
| 5 | D-440 | USB hub | $29 |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #N/A | The value isn't in lookup_array. Look for extra spaces or numbers stored as text, or add an if_not_found argument so the formula says so plainly. |
| #VALUE! | lookup_array and return_array are different sizes. Make both ranges start and end on the same rows. |
| #NAME? | Your version doesn't have XLOOKUP (Excel 2019 or earlier). Use INDEX and MATCH, which work everywhere. |
| Wrong results after filling down | The ranges shifted as the formula moved. Lock them with $, for example $A$2:$A$5. |

## Tips

- Return several columns at once by making return_array wider, such as B2:C5. The results spill to the right.
- Use search_mode -1 to get the most recent entry from a log that is sorted oldest first.

## Related functions

- [VLOOKUP](https://spreadsheetpractice.com/functions/vlookup): VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row.
- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
- [MATCH](https://spreadsheetpractice.com/functions/match): MATCH returns the position of a value in a row or column: 3 means the third cell.
- [XMATCH](https://spreadsheetpractice.com/functions/xmatch): XMATCH is the modern MATCH: it returns the position of a value in a list, but it defaults to an exact match, can search from the bottom up, and handles wildcards and next-larger or next-smaller matches without sorted data.
- Comparison: [VLOOKUP vs XLOOKUP](https://spreadsheetpractice.com/functions/compare/vlookup-vs-xlookup)
- Comparison: [XLOOKUP vs INDEX and MATCH](https://spreadsheetpractice.com/functions/compare/xlookup-vs-index-match)

## Questions

### Is XLOOKUP better than VLOOKUP?

For most work, yes. It doesn't need a column number, it can return columns to the left of the search column, and it defaults to an exact match, which avoids VLOOKUP's most common silent error.

### Does XLOOKUP work in Excel 2019?

No. It's available in Excel 2021, Excel 2024 and Microsoft 365. Files that use it show #NAME? in older versions.

### Can XLOOKUP match on two criteria?

Yes. Join the criteria with &, for example =XLOOKUP(G2&H2, A2:A9&B2:B9, C2:C9).

## Practice

A graded XLOOKUP exercise ("ID to name") is on [the XLOOKUP page](https://spreadsheetpractice.com/functions/xlookup). It is checked instantly, then re-checked on changed data.

Lessons that use XLOOKUP:

- [Lesson 13: XLOOKUP](https://spreadsheetpractice.com/learn/xlookup) (Pro)
- [Lesson 15: Tiered lookups & IFERROR](https://spreadsheetpractice.com/learn/tiered-lookups-iferror) (Pro)
- [Lesson 16: Lookups with several criteria](https://spreadsheetpractice.com/learn/multi-criteria-lookups) (Pro)
- [Lesson 17: Last match, nearest value & wildcards](https://spreadsheetpractice.com/learn/xlookup-search-modes) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
