XLOOKUP in Excel: the lookup formula that just works

XLOOKUP finds a value in one column and returns the value next to it from another column. It replaces VLOOKUP and is easier to write.

The formula

=XLOOKUP(what_to_find, where_to_look, what_to_return)

Example: find a price

=XLOOKUP("Bag", A1:A3, B1:B3)

Result: 15.00. Excel finds “Bag” in column A and returns the same row from column B.

In real sheets the thing to find sits in a cell: =XLOOKUP(D1, A:A, B:B) looks up whatever is typed in D1.

Show a message when nothing is found

=XLOOKUP(D1, A:A, B:B, "Not found")

The fourth part replaces the ugly #N/A error with your own text.

Why it beats VLOOKUP

• The return column can be to the left of the lookup column. VLOOKUP cannot do that.
• No counting columns. You point at the return column directly.
• Exact match is the default. VLOOKUP needs FALSE at the end or it guesses.
• Inserting a column between lookup and return does not break it.

Needs Excel 2021 or Microsoft 365

Older versions do not have XLOOKUP. If your formula shows #NAME?, use VLOOKUP or INDEX MATCH instead.