VLOOKUP vs XLOOKUP: Which One to Use and How to Get the Most from Them

Looking up a value in a table based on another value is one of the most common operations in Excel: a product’s price from its code, a customer’s email from their number or an employee’s department from their name. For decades the star function for this was VLOOKUP. Since 2019, Excel has included XLOOKUP, a more modern and flexible function. In this article we compare both and explain when to use each one.

How VLOOKUP works

VLOOKUP searches for a value in the first column of a range and returns the value from another column in the same row.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example: you have a product table in A2:C100 with code, name and price. To get the price for the code in F2:

=VLOOKUP(F2,$A$2:$C$100,3,FALSE)

The last argument, FALSE, means you want an exact match. Forgetting it is one of the most common mistakes: without it, Excel looks for an approximate match and may return a wrong value without any warning.

VLOOKUP’s limitations

• It only looks to the right: the lookup column must be the first column of the range. If the code is in column C and you want the name from column A, it does not work directly.
• Fixed column number: if you insert a column in the middle of the table, the number 3 no longer points to the right column and the formula returns wrong data.
• Approximate match by default, which causes errors if you forget FALSE.
• It returns #N/A when the value is not found, so you need to wrap it in IFERROR to show something friendlier.

How XLOOKUP works

XLOOKUP separates the column you search in from the column you want to return:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The same example becomes:

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,"Not found")

XLOOKUP’s advantages

• It looks in any direction: the return column can be to the left or right.
• Exact match by default, which avoids VLOOKUP‘s most common mistake.
• It does not break when you insert columns, because the references are ranges, not column numbers.
• Built-in not-found value, with no need for IFERROR.
• It can search from the bottom, handy for finding a customer’s latest record.
• It can return several columns at once if you select a multi-column range as the return array.

Comparison table

Feature	VLOOKUP	XLOOKUP
Available in	Every version	Excel 365 and 2021 or later
Lookup direction	Right only	Any
Default match	Approximate	Exact
Survives inserted columns	No	Yes
Not-found value	Needs IFERROR	Built in
Find the last value	No	Yes

What about INDEX + MATCH?

Before XLOOKUP existed, advanced users combined INDEX and MATCH to overcome VLOOKUP‘s limitations:

=INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0))

It works in any direction and does not break when columns are inserted. It is still a good choice if your files must work in older versions of Excel or in programs that do not support XLOOKUP.

Which one should you use?

• Use XLOOKUP if everyone who will open the file has Excel 365 or 2021 or later. It is clearer, safer and more powerful.
• Use INDEX + MATCH if you need compatibility with older versions and want flexibility.
• Use VLOOKUP for simple lookups in files that must open in any version, always with FALSE at the end.

If a file using XLOOKUP is opened in a version that does not support it, the cells will show the #NAME? error.

Common lookup mistakes

1. Extra spaces: “A001 ” does not match “A001”. Clean them with TRIM.
2. Numbers as text: the code 1001 stored as text does not match the number 1001.
3. Relative references: when you copy the formula down, the range shifts. Use $ to lock it.
4. Duplicates in the lookup column: both functions return only the first match. Remove duplicates with our online duplicate remover if they should not be there.

Lookups and file comparison

Lookup functions are the basis for comparing two lists by a key: you look up each code from the new list in the old one and compare the values. It works well for one or two columns, but gets complicated with large tables and several sheets. In those cases, the Excel comparison tool on our Excel tools page gives you the full result without writing a single formula.

Frequently asked questions

Conclusion

VLOOKUP is still useful and universal, but XLOOKUP is its natural successor: it looks in any direction, uses exact match by default and does not break when the table changes. If you work with older versions, INDEX and MATCH are the flexible alternative. Mastering these functions will save you hours every week. And to compare whole files without formulas, visit our Excel tools page.