What is XLOOKUP in Excel? Differences with VLOOKUP and how to switch

What is XLOOKUP in Excel? Easy explanation of the difference with VLOOKUP and how to switch

Explains Excel's XLOOKUP function, including the differences from VLOOKUP. We have summarized the advantages of XLOOKUP and how to switch to it in an easy-to-understand manner, such as the fact that the search column does not need to be on the left edge, and error handling is easy.

中

中村健太

2026-02-15

XLOOKUP will solve all the inconveniences you had with VLOOKUP

The VLOOKUP function is used when "pulling data from another table" in Excel. It's convenient, but have you ever had any complaints like this?

Cannot be used unless the search column is on the left edge
It's a pain to count the column numbers (and they shift when adding columns)
When it is not found, the error "#N/A" appears and it looks bad

This can all be resolved with XLOOKUP. This is a new function that can be used with Microsoft 365 and Excel 2021 and later.

Basic way to write XLOOKUP

Start with the simplest usage.

=XLOOKUP(search value, search range, return range)

For example, if you want to get the product name of product code "A001" from another table:

=XLOOKUP(A2, Product Master!A:A, Product Master!B:B)

Let's compare with VLOOKUP:

	VLOOKUP	XLOOKUP
How to write	`=VLOOKUP(A2, product master!A:C, 2, FALSE)`	`=XLOOKUP(A2, product master!A:A, product master!B:B)`
Column number	Required (2nd column = 2)	Not required (specify directly in range)
Search column position	Left edge only	Anywhere
If not found	#N/A error	Alternative value can be set

3 useful points of XLOOKUP

1. Search column does not need to be on the left
In VLOOKUP, the search column had to be on the leftmost side. With XLOOKUP, you specify the search range and return range separately, so you don't have to worry about column placement.

2. You can set an alternative value when not found.
If you want to display "Not Found" instead of the #N/A error:

=XLOOKUP(A2, Product Master!A:A, Product Master!B:B, "N/A")

There is no need to enclose it in IFERROR. It's downright extremely convenient.

3. Exact match is the default
VLOOKUP often forgot to set the fourth argument to "FALSE", resulting in an approximate match. XLOOKUP is an exact match from the beginning, so it is safe to omit arguments.

Steps to transfer from VLOOKUP

There is no need to rewrite everything all at once. Recommended procedure:

Write new formulas in XLOOKUP. Existing VLOOKUP can be left as is
When possible, replace the VLOOKUP expression with the error with XLOOKUP
When sharing a file with someone who uses VLOOKUP, check the Excel version of the other person (Not compatible with Excel 2019 or earlier)

Note: XLOOKUP cannot be used in Excel 2019 or earlier or in some Google Sheets environments. For shared files, it may be safe to leave it as VLOOKUP.

FAQ

Can XLOOKUP be used with Google Spreadsheets?

Yes, Google Sheets will also be supported from 2023. The writing method is the same as in Excel.

Do I no longer need to use VLOOKUP?

XLOOKUP is recommended for new formulas, but VLOOKUP is not obsolete. There is no need to forcefully rewrite existing files.

Can I search with multiple conditions using XLOOKUP?

Although not directly supported, it is possible to perform a pseudo multiple condition search by combining the search value and search range with "&". Example: =XLOOKUP(A2&B2, table!A:A&table!B:B, table!C:C)