Excel + Google Sheets · Deep guide
XLOOKUP in Excel: the modern way to look up values
XLOOKUP replaces VLOOKUP in modern Excel. It searches one column and returns the matching value from another column, in any direction, without counting column numbers, and it can show a custom message when the key does not exist.
XLOOKUP needs Excel 2021 or Microsoft 365, and it also works in Google Sheets. It does not work in Excel 2019 or earlier, so if you share files with people on older versions, use VLOOKUP instead. Here you will see the syntax, a worked example and the usual mistakes. See also: VLOOKUP guide and COUNTIF guide.
How XLOOKUP works
XLOOKUP takes the value to find, the range to search, the range to return and, optionally, a value to show when nothing matches. Write it like this:
=XLOOKUP("c",B2:B6,C2:C6,"none")Excel searches column B for the letter c and returns the value from the same row of column C. If c did not exist, the cell would show the word none instead of an error. Works in Excel 2021 or Microsoft 365.
Step by step example
- Type the keys in column B and the values in column C.
- Click an empty cell, for example E2.
- Type
=XLOOKUP("c",B2:B6,C2:C6,"none")and press Enter. - Compare the result with the table below.
| A | B | |
|---|---|---|
| 1 | Category (B) | Price (C) |
| 2 | a | 10 |
| 3 | b | 20 |
| 4 | c | 30 |
| 5 | b | 40 |
| 6 | d | 50 |
The expected result is 30. Matching is exact by default, so you do not need to add FALSE. If the key appears twice, XLOOKUP returns the first match unless you change the search mode.
Common errors
- The ranges to search and to return have different sizes. Both must have the same number of rows.
- You use XLOOKUP in a file opened with Excel 2019 or earlier and the cell shows a name error.
- The key has extra spaces, so it never matches. Clean the column with TRIM.
- Numbers stored as text do not match real numbers. Convert both sides to the same type.
Because XLOOKUP spills results and handles missing keys by itself, it makes sheets shorter and easier to audit. Keep VLOOKUP only for files that must open in older Excel versions.