Bottom Line: Find out why it's still important to have a healthy knowledge of VLOOKUP, even though XLOOKUP has replaced it.
Skill Level: Beginner
Watch the Tutorial
Download the Excel File
The workbook that I use in the video can be found here:
XLOOKUP Replaces VLOOKUP
XLOOKUP was released in 2019 as the successor to VLOOKUP. It is superior to VLOOKUP and Index Match in almost every way, so it's natural that your preference would be to use XLOOKUP if it's available to you. But that doesn't necessarily mean that you shouldn't know VLOOKUP. I'll tell you why in a moment.
First, let's take a look at why Microsoft created XLOOKUP to replace VLOOKUP and Index Match. Three main reasons are:
- VLOOKUP can break when you insert or delete columns.
- VLOOKUP defaults to the closest match whereas XLOOKUP defaults to an exact match. To correct that in VLOOKUP, you have to type FALSE as your fourth argument.
- Index Match is a more complex formula than XLOOKUP, requiring two functions in the formula instead of just one.
When to use VLOOKUP instead of XLOOKUP
Are there scenarios when you would use VLOOKUP instead of XLOOKUP?
The first reason has to do with compatibility. XLOOKUP is available on Microsoft 365 and Excel 2021. That means if your users are on Excel 2019 or earlier, XLOOKUP is not an option for them.
Since XLOOKUP is not backward compatible, if you send a file that uses XLOOKUP to those users, they are not going to be able to use it. Initially, they will be able to see the results of the XLOOKUP formula in your file, but any recalculation or modification of the formula will result in a #NAME error.
Even if everyone in your organization uses a current version of Excel, you can still encounter this compatibility issue with external vendors, clients, and peers.
The other reason you may want to use VLOOKUP has to do with knowledge. Just as users both inside and outside of your organization may not have XLOOKUP, they may not know how to use it.
Of course, others' lack of knowledge about XLOOKUP could open up some great opportunities for you to show them how it works. Some might be intimidated because XLOOKUP has 6 arguments whereas VLOOKUP only has 4.
If you want to help teach your coworkers how to use XLOOKUP, you can send them links to these tutorials:
- XLOOKUP for Excel: Explained in 3 Minutes
- The New XLOOKUP Function: Compared to VLOOKUP & INDEX MATCH in Excel
- VLOOKUP Tutorial for Excel – Everything You Need To Know
- INDEX MATCH Explained
Do I still need to learn VLOOKUP?
Again the answer is yes!
Look at this article announcing the release of XLOOKUP in 2019:
If VLOOKUP has been in use for 35 years and is the third most popular function, you can guarantee that there are billions of VLOOKUP formulas out there. XLOOKUP may be the way of the future, but VLOOKUP is a big part of the past. So for the present, it's still important that you know how VLOOKUP works because you will come across it in your Excel adventures.
In summary, you'll still want to use VLOOKUP (or INDEX/MATCH) when compatibility or users' knowledge might be an issue. XLOOKUP is the way of the future, but we are in a long transition period and it could take many years for it to become the most common lookup formula.
I hope this explanation is helpful for you. If you have questions, thoughts, or ideas on this topic, we'd love to read them in the comments below.
Have a great week!