XLOOKUP vs VLOOKUP: When to Use Each (and Why XLOOKUP Usually Wins)

VLOOKUP's Fundamental Limitations

Can only look right: VLOOKUP finds a value in the leftmost column, then returns a value from a column to the right. If your lookup value is in column C and your return value is in column A, VLOOKUP can't help.

Column index breaks: =VLOOKUP(A1, Table, 3, FALSE) returns the 3rd column. Insert a column in your table? Every VLOOKUP using that table now returns wrong data.

Exact match isn't default: The fourth parameter (FALSE for exact match) is easy to forget. Miss it and you get approximate matching, which produces silent errors.

How XLOOKUP Solves These Problems

Look any direction: XLOOKUP specifies lookup and return ranges separately. Look in column C, return from column A? No problem.

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array)

No column index: You specify the exact return range, not a column number. Insert columns anywhere — your formula still works.

Exact match is default: No parameter needed for the most common use case.

Built-in “if not found”: =XLOOKUP(A1, B:B, C:C, "Not found") returns your custom message instead of #N/A.

XLOOKUP Examples

Basic lookup:

=XLOOKUP(D2, A:A, B:B)

Find D2's value in column A, return corresponding value from column B.

With error handling:

=XLOOKUP(D2, A:A, B:B, "Customer not found")

Returning multiple columns:

=XLOOKUP(D2, A:A, B:C)

Returns two columns of data. Spills to adjacent cells.

When to Still Use VLOOKUP

• Compatibility: XLOOKUP requires Microsoft 365 or Excel 2021+. If your file will be used in older versions, stick with VLOOKUP.
• Existing formulas: If a workbook has 500 VLOOKUPs that work correctly, don't rewrite them just for style.
• Simple cases: For basic lookups where data won't change structure, VLOOKUP works fine.

Official Resources

• XLOOKUP function — complete syntax and examples
• VLOOKUP function — classic lookup reference