For a new workbook in Microsoft 365 or Excel 2021 and later, XLOOKUP is generally easier to maintain. For workbooks shared with Excel 2019 or older, VLOOKUP remains a compatible choice. Both can return the same value, but their arguments and limitations differ.
Compare equivalent formulas
With SKU in E2, IDs in A2:A100, and prices in C2:C100, use =VLOOKUP(E2,A2:C100,3,FALSE) or =XLOOKUP(E2,A2:A100,C2:C100,"Not found"). Both return the price when the SKU exists.
What happens when the key is missing?
VLOOKUP with FALSE returns #N/A for an unmatched key. XLOOKUP accepts an optional fourth if_not_found argument, such as "Not found". For VLOOKUP, wrap the formula in IFNA to get the same display.
Left lookups and inserted columns
VLOOKUP searches only the first column of its table array and counts the return column by index. XLOOKUP uses separate lookup and return arrays, allowing leftward lookups and avoiding fragile column indexes when the table structure changes.
Compatibility
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or earlier. VLOOKUP works in older Excel versions. Confirm the oldest Excel version used by your collaborators before switching.
Choose based on the workflow
Keep a stable VLOOKUP in a shared legacy workbook. Prefer XLOOKUP for new Microsoft 365 files, particularly when return columns can move or the lookup must work in either direction. Always test a known match and a missing value.
Try the matching tools
Build a formula with the Excel formula builder, VLOOKUP generator, or XLOOKUP generator. Browse the formula guides for related problems.
Frequently asked questions
Is XLOOKUP always better than VLOOKUP?
No. XLOOKUP has a clearer interface, but VLOOKUP may be necessary for older Excel workbooks.
Does XLOOKUP default to exact match?
Yes. XLOOKUP defaults to exact match, whereas VLOOKUP defaults to approximate match if its fourth argument is omitted.