XLOOKUP lets you select lookup and return arrays separately. It uses exact matching by default, can return values from the left or right, and can show a clear message when no record is found.
Why is XLOOKUP more flexible?
VLOOKUP requires the key to be the first column and asks for a column number. XLOOKUP points directly to the lookup array and the return array.
Its syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
Look up a price by product code
The formula below searches for the code in A2 within column F and returns the price from column H. Its fourth argument replaces #N/A with a readable message.
Keep the lookup and return arrays the same size, copy the formula down, and test known codes.
How to do it
- Select the lookup value.
- Select the range containing the keys.
- Select the return range and add an optional not-found message.
=XLOOKUP(A2,$F$2:$F$100,$H$2:$H$100,"Not found")Verify the result with four product rows
Put P-101, P-102, P-103 in A2:A4; Pen, Notebook, Bag in B2:B4; and 50, 245, 400 in C2:C4. If E2 contains P-102, the formula returns 245. Lookup and return arrays must cover matching rows.
If E2 is P-999, the displayed result is 'Not found'. That is a prompt to check a missing code, not evidence that the record exists. If the source repeats a code, verify which matching record is returned.
| Code (A) | Product (B) | Price (C) |
|---|---|---|
| P-101 | Pen | 50 |
| P-102 | Notebook | 245 |
| P-103 | Bag | 400 |
=XLOOKUP(E2,A2:A4,C2:C4,"Not found")When is XLOOKUP different from VLOOKUP?
VLOOKUP requires the return column to the right of the key. XLOOKUP selects lookup and return arrays separately, so it can return a value from the left. If E2 contains price 245, you can search C2:C4 and return code P-102 from A2:A4—but price is a poor key when values repeat.
Availability depends on the recipient's Excel version. Check compatibility when sharing older workbooks. SheetCommand does not write XLOOKUP formulas; its two-sheet matching action is limited to supported exact-key value matching.
Common XLOOKUP checks
Arrays with different sizes cause an error. Number-versus-text differences and hidden spaces can also prevent a match.
XLOOKUP is unavailable in some older Excel versions. Consider VLOOKUP or INDEX and MATCH when sharing with users on older software.
Frequently asked questions
Does XLOOKUP use exact matching by default?
Yes. Its default match mode is exact match.
Can SheetCommand add XLOOKUP to my file?
No. The guide explains the formula; SheetCommand currently cleans, sorts, updates, and summarizes source data.
Sources and further reading
SheetCommand
Prepare lookup data before using XLOOKUP
Remove duplicate and completely blank rows, then review the source-file summary.