Introduction
Excel offers avast number of functionsthat cater to different users and their unique requirements.
Among these functions, VLOOKUP has long been the go-to choice for basic lookups in a table or range.
In almost every industry, millions and millions of existing spreadsheets use VLOOKUP to do something useful.
However, with the introduction of XLOOKUP in 2019, Excel users have a powerful new lookup option available.
XLOOKUP can do everything VLOOKUP can do, and much more.
Should you stop using VLOOKUP altogether?
Should you even learn VLOOKUP if you are new to Excel?
Let’s have a look at the pros and cons of XLOOKUP and VLOOKUP.
As the name implies, VLOOKUP is designed to work withvertical data.
For more VLOOKUP examples and videossee this page.
VLOOKUP Pros
Intuitive operation:VLOOKUP scans through the first column in the table.
With a small number of inputs, VLOOKUP is easy and intuitive.
All VLOOKUP needs is a lookup value, the table address, and a column number.
VLOOKUP Cons
While VLOOKUP is popular and easy to use, it does have some real limitations.
However, to be fair, youcancombine VLOOKUP with the MATCH functionto perform a dynamic 2-way lookup.
This means VLOOKUP will simply return a #N/A error when a lookup fails.
To trap and handle this error, you must use another function like IFERROR or IFNA.See an example here.
There is no simple way to get VLOOKUP to perform a reverse search.
The most basic workaround is toadd a helper column with concatenated values.
A more advanced approach involvescreating a new lookup tableon the fly.
It is a flexible and versatile function that can be used in a wide variety of situations.
For more XLOOKUP examples and videossee this page.
XLOOKUP Pros
Sensible defaults:Unlike VLOOKUP, XLOOKUPdefaults to an exact match.
This is a much safer default because a user must explicitly enable approximate match behavior when needed.
Normal column reference:XLOOKUP uses a normal cell reference for thereturn_array.
This means XLOOKUP is less fragile than VLOOKUP because ordinary changes to the table structure (i.e.
inserting or deleting columns) will not break the formula.
In both cases, datadoes notneed to be sorted.
See abasic example here.
See a more advancedclosest-match example here.
There is no need to use another function likeIFERROR.See an example here.
Easy to apply multiple criteria: The structure of XLOOKUP makes it straightforward to apply multiple criteria.
The trick is to create alookup_arraywithBoolean algebra, then set thelookup_valueto 1 (basic example,advanced example).
XLOOKUP cons
Limited availability:XLOOKUP is only available in the latest versions of Excel.
This means XLOOKUP will not work if a worksheet is opened in an older version of Excel.
This is mostly because XLOOKUP provides many more features than VLOOKUP.
Two-way lookups are more complex:Compared to VLOOKUP and INDEX and MATCH, a two-way lookup (i.e.
looking up both a row and column in the same formula) with XLOOKUP is more complicated.
Feature comparison
The table below summarizes the key differences mentioned above.
Summary
While VLOOKUP has been widely used in Excel for many decades, it has real limitations.
The XLOOKUP function has been designed to address these limitations head-on.
In almost every respect, XLOOKUP is a better and more powerful lookup function.
There is no burning need to replace existing VLOOKUP solutions with XLOOKUP unless the existing configuration is unnecessarily complex.
In other words, VLOOKUP is not broken; it is simply limited.
XLOOKUP is only available in Excel 2021 and later.
With the above in mind, I recommend that you start using XLOOKUP for your lookup problems.
XLOOKUP takes a little more practice because it has more features and options.
