XLOOKUP
Finds a value in one column and returns data from another: a price by product ID, a phone number by last name, even when the answer is to the left of the key.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])- [ ]optional part
- lookup_value
- What to look for: the cell with the key, for example a product ID in A2.
- lookup_array
- The column to search for the key, for example $E$2:$E$5.
- return_array
- The column with the answers, the same height. The value is taken from the row where the key was found.
- if_not_found
- What to show if the key isn't found. If you leave it out, you get #N/A.Optional
Return value
The value from the return column in the row where the key was found. If the key isn't found, the function returns the fourth argument, or #N/A without it.
Examples
Product by ID, answer to the left of the key
Details
XLOOKUP looks for a key in one column and returns the value from the same row of another column. You point to the columns separately, so there's no column number to count as in VLOOKUP, and the answer can be to the right or to the left of the key.
By default the function looks for an exact match. If the key isn't there, it returns #N/A or the text from the fourth argument, for example "Not in catalog".
The function is available in Excel 2021 and Microsoft 365. Excel 2019 and earlier don't have it, and the formula shows #NAME? there.
Common mistakes
- The lookup and return columns have different heights or start on different rows. The function returns an error or a value from the wrong row.
- Without $ signs the ranges shift when you fill down, and the lower rows get #N/A.
- The file is opened in Excel 2019 or earlier: #NAME? appears instead of the result. For such files, use VLOOKUP or INDEX with MATCH.