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?
Yes!
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.
Conclusion
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!
As usual, Jon, nicely done! One thing I love about xlookup() is the value if not found argument. That simplifies vlookup() even more as you don’t have to trap for that nasty #N/A error.
Before everyone gets too happy for xlookup (as i used to be) let me tell you about a fact that may put a damper on your enthusiasm.
The xlookup is good for small excercises. But if you use it in big excercises be prepare for your spreadsheet to slow down to a crawl.
And if you expand on the xlookup, by putting in extra arguments (i.e. using concatenations inside of the formula) to bring in additional answers, the performance gets soooooooooooooo slow that you can actually go on a lunch break, come back, take a shower, and the darn thing will still be working….that’s how slow it gets. I little exaggeration was added for effect….you probably don’t have time for a shower, but I think you get what I’m trying to convey.
I was SSSSSOOOOOO excited to use the xlookup and was pleased until I started to modify it. Now i don’t hardly touch it anymore. Waste of time. AT least for me. Vlookup blows it away with its proven performance,
Bob
In every test I’ve ever seen or personally conducted, INDEX-MATCH is at least as fast as VLOOKUP if not faster. Plus INDEX-MATCH is more flexible and robust.
When choosing among VLOOKUP, INDEX/MATCH, XLOOKUP, the running speed may be a major factor considered. An example is a workbook of 30 worksheets each have a large range (say 1000 rows, 10 columns) containing calculation using Lookup formula in each cell, and the Lookup data range, which can be more than one, is also large (say 1000 rows, 10 columns). Which function is the fastest in recalculation after input of the Lookup data ranges ?
A very good analysis and comparison. Can’t wait to get Excel 2021!
Hi. Is it possible to do something with XLOOKUP where we only want a return if the data in a specific column contains certain text? Currently using =IF(ISNUMBER(SEARCH(“Nov”,ALL!$I4)),XLOOKUP($A4,ALL!$A:$A,ALL!BC:BC),””), to only return data where I4 in the All sheet contains “Nov”, but end users don’t understand the formula and try to change it!
I can’t think of any way for XLOOKUP to handle that scenario on its own. And even if I could, it would probably involve some trick that your users also wouldn’t understand and might also try to change. If your users are going micro-analyze your formulas and try to change anything they don’t understand, then you’re definitely in a tight spot.
Before everyone buys wholeheartedly into the statement that XLOOKUP is superior to INDEX MATCH, don’t forget that Excel 365 also has XMATCH with the same default exact match behavior as XLOOKUP and the same match_mode and search_mode arguments.
Also, any thought that VLOOKUP (which is known to be an inferior function) should be considered for even a millisecond because it’s better and more widely known is shortsighted. People will happily wait in line for hours or even days to spend hundreds or thousands of dollars for the “privilege” of learning all the things that are new and different about the latest iPhone. And then they’ll do it again when the next one comes out. Apple doesn’t just maintain the status quo because millions of people love their latest phone. They expect their users to learn, adapt, and keep up. So don’t tell me those same people can’t be bothered to learn the differences between VLOOKUP and INDEX MATCH as a once and forever done exercise.
Additionally, the fact that INDEX and MATCH are two separate functions is what makes them so flexible. Let’s say you need to return multiple values from the same row of a lookup involving a very large dataset. For VLOOKUP and XLOOKUP, every lookup (every attempted match) is just as difficult as the one before, which takes time for the repeated effort. But with INDEX and MATCH, the MATCH (the hard part of any lookup) can be done once in a single helper cell and then reused for multiple INDEX functions to return those myriad values in a fraction of the time. And if multiple functions equals a concerning amount of complexity, then people should immediately stop nesting one IF inside of another…except that they won’t because most people don’t consider that to be very complex. So “different” is not the same thing as “complex.”
Lastly, there are still a few things INDEX XMATCH can do where XLOOKUP either struggles or fails entirely. What if you need to return the value from a certain number of rows above or below the match location? That’s a common enough and uncomplicated scenario. With XMATCH, you simply and easily add or subtract the appropriate number to offset, but XLOOKUP can’t do that. Or what about simultaneously returning multiple values for multiple simultaneous lookups? That can be accomplished with a formula like this:
=INDEX(CHOOSE({1,2},B2:B7,D2:D7),XMATCH(F4:F6,C2:C7),{1,2})
But I’m unaware of a working direct XLOOKUP equivalent. Yes, this is a more uncommon and complex scenario, but the point is that INDEX XMATCH can handle both of these scenarios while XLOOKUP can’t…at least not without possible help from other functions.
Please don’t get me wrong. XLOOKUP is a massive step in the right direction for anyone who has never made the switch to INDEX MATCH. But let’s be careful not to misrepresent the full extent of its capabilities (or lack thereof) in comparison to INDEX XMATCH.
Need Data validation