XLOOKUP vs VLOOKUP: which one should you use?

Both functions connect a key to a result

Lookup formulas answer questions such as: which department belongs to this employee ID, or what price matches this product code? The lookup value is the key, and the return range contains the answer.

XLOOKUP separates the lookup range from the return range, which makes the formula easier to read and less fragile when columns move.

=XLOOKUP(A2, Products[Code], Products[Price], "Not found")

Why XLOOKUP is usually the clearer choice

XLOOKUP can return values from either side of the key, uses exact matching by default, and lets you define a friendly not-found result. VLOOKUP relies on a column number and expects the lookup column on the left.

• Choose XLOOKUP for new Microsoft 365 workbooks.
• Use exact matching unless the business rule explicitly needs ranges.
• Keep the lookup and return arrays the same size.
• Use a not-found message that helps the person reviewing the sheet.

Why VLOOKUP still matters

Older files, assessment tasks, and organisations using earlier Excel versions may still depend on VLOOKUP. You do not need to prefer it, but you should recognise its arguments and understand why FALSE is commonly used for an exact match.

When maintaining an existing workbook, consistency and compatibility may matter more than replacing every older formula immediately.