How to use XLOOKUP in Excel
Searches one column and returns the matching value from another.
Syntax
lookup_valueThe value to findlookup_arrayThe column to searchreturn_arrayThe column to return from[if_not_found]What to show if nothing matches optionalExample
Here’s XLOOKUP on a small table of orders. The highlighted cells are the ones the formula reads.
=XLOOKUP(150,F2:F9,A2:A9)Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with XLOOKUP
Ranges must be the same size
The lookup range and return range need the same number of rows, or you get #VALUE!.
Use the if_not_found argument
Add a fourth argument like "Not found" instead of wrapping in IFERROR.
Not available in Excel 2019 or older
XLOOKUP needs Excel 2021, Microsoft 365 or Excel for the web. Use INDEX/MATCH for older versions.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=XLOOKUP(150,F2:F9,A2:A9
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Questions
What does the XLOOKUP function do in Excel?
Searches one column and returns the matching value from another. The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]).
Why is my XLOOKUP formula giving the wrong answer?
Ranges must be the same size: The lookup range and return range need the same number of rows, or you get #VALUE!. Use the if_not_found argument: Add a fourth argument like "Not found" instead of wrapping in IFERROR. Not available in Excel 2019 or older: XLOOKUP needs Excel 2021, Microsoft 365 or Excel for the web. Use INDEX/MATCH for older versions.
Does XLOOKUP work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.