Lookup & reference
How to Use XLOOKUP in Excel (the Modern VLOOKUP)
XLOOKUP is the modern lookup function in Excel. It replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH formulas with one cleaner function.
It can look in any direction, return a built-in "not found" message, and never breaks when you insert a column. This guide shows the syntax and the patterns you will actually use.
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) | Argument | Required | What it does |
|---|---|---|
lookup_value | Required | The value to search for. |
lookup_array | Required | The single column (or row) to search in. |
return_array | Required | The column (or row) to return a value from. It can be to the left of the lookup array. |
if_not_found | Optional | What to return when there is no match, e.g. "Not found". Avoids #N/A entirely. |
match_mode | Optional | 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard. |
search_mode | Optional | 1 = first to last (default), -1 = last to first (find the most recent match). |
Note: Available in Microsoft 365, Excel 2021+, and Google Sheets. Older versions show #NAME?.
Examples
Real, copy-paste-ready formulas.
1. Basic lookup
=XLOOKUP(E2, A:A, C:C) Searches column A for the value in E2 and returns the matching value from column C. No column counting, no FALSE flag needed. Exact match is the default.
2. Built-in "not found" message
=XLOOKUP(E2, A:A, C:C, "Not found") The fourth argument replaces #N/A, so you do not need to wrap the whole thing in IFERROR.
3. Look to the LEFT (impossible with VLOOKUP)
=XLOOKUP(E2, C:C, A:A) Because the return array is independent of the lookup array, XLOOKUP happily returns a column to the left. That is the classic VLOOKUP limitation, solved.
How to write XLOOKUP step by step
- 1
Click the result cell and type =XLOOKUP(
- 2
Click the cell with the value you are searching for, then a comma.
- 3
Select the column to search in (the lookup_array), then a comma.
- 4
Select the column to return a value from (the return_array).
- 5
Optionally add a comma and a "Not found" message, then close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#N/A | No match was found and no if_not_found value was supplied. | Add a fourth argument like "Not found", or check for stray spaces / mismatched data types. |
#VALUE! | lookup_array and return_array are different sizes. | Make sure both ranges have the same number of rows (or columns). |
#NAME? | Your Excel version does not have XLOOKUP. | Update to Microsoft 365 / Excel 2021+, or fall back to INDEX/MATCH or VLOOKUP. |
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
For most people, yes. XLOOKUP can look left or right, returns a clean "not found" message, defaults to exact match, and does not break when you insert columns. The main reason to still use VLOOKUP is compatibility with very old Excel versions.
Which Excel versions have XLOOKUP?
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It is not in Excel 2019 or earlier. Google Sheets also supports XLOOKUP.
Can XLOOKUP return multiple columns?
Yes. If you select multiple columns as the return_array, XLOOKUP spills the matching values across several cells in modern Excel.