How to use XLOOKUP to pull a price from another sheet
You have your orders on one sheet and a price list on another. This matches each product code and brings its price across, so you never copy prices by hand again.
The formula
=XLOOKUP(B2,Sheet2!A:A,Sheet2!B:B,"Not found") in column C
What each part does
B2
The value to look up, taken from this row. Here it is the product code you want the price for.
Sheet2!A:A
Where to search: column A on the other sheet, the list of product codes. Sheet2! means look on that sheet, not this one.
Sheet2!B:B
What to bring back once the code is found: the matching price in column B, lined up next to the codes.
"Not found"
What to show if the code is not on the other sheet. Without this last part XLOOKUP shows the error #N/A instead.
How it works, and what to watch for
Sometimes it returns #N/A even though you can see the code on the other sheet. The cause is almost always one of two things. Either there is a stray space, or the code is saved as text on one side and as a number on the other. Wrapping both sides in TRIM clears the spaces; matching the types clears the rest.
VLOOKUP can only look to the right of the column it matches on. XLOOKUP has no such rule, which is why it is the one shown here.
There is no XLOOKUP before Excel 2021. If you are on an older version, switch the version setting and you will get a VLOOKUP version that does the same job.
Questions
Why does my lookup say #N/A when I can see the value right there?
To Excel the two values are not quite the same. Usually one has a space on the end you cannot see, or one is a number while the other is text. TRIM and matching the types sorts it out.
Should I use XLOOKUP or VLOOKUP?
XLOOKUP if you have Microsoft 365 or Excel 2021: it can look left, survives an inserted column, and lets you set your own "not found" message. On older Excel, VLOOKUP is the one to reach for.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
