A lot of us are familiar with this function. You could even make a case that it’s the most (in)famous Excel function out there: Vlookup. The Vlookup function (Vlookup stands for Vertical Lookup) is widely used in business and personal Excel use alike. Whenever a value needs to be found in a long list of entries, a formula containing Vlookup is probably the first thing to come to mind.
It can be complicated for beginning Excel users to wrap their heads around, but once learned, Vlookup can be used as a powerful and versatile tool. It can save you hours of time by looking up values that do not have to be found by hand.
However, there have always been problems with Vlookup. Drawbacks that any advanced Excel user can tell you about. And now that a new function has been introduced that does not suffer from the same problems, Vlookup may not be the function that you want to be using anymore.
What’s wrong with Vlookup?
Let’s make one thing clear: Vlookup is still a useful function. It does what it should do and many workbooks thrive with it. But there are two main problems that you don’t see from the surface that can get in your way.
The first issue is that Vlookup only looks up values from the leftmost column. If you have a table where you want to return an entry based on a value that is not in the leftmost column, you cannot use Vlookup. Alternatively, you can reorganize your table and change the column order to have the column that you need as the leftmost column. But who wants to completely reorganize all their data due to an arbitrary limitation of Excel?
Then there’s the second issue, which is that Vlookup is not capable of looking up values to the left of the lookup value. Here’s an example to illustrate this:
Suppose you have a table containing first names and last names, both in their own column. If you are interested in looking up a first name and showing the corresponding last name, you could use Vlookup. That all works out well.
However, if instead of using the first name to look up the last name, you would want to use the last name to look up the first name, things go wrong. Vlookup is not able to look up a value in a column that’s to the left of the known value. So if the first name column is to the left of the last name column, Vlookup cannot return it.
There are more problems with Vlookup, but we won’t go into those in this post because it would make for a way longer post than necessary. Although if you want to know more about this, you can read all about them in the Drawbacks section of our Vlookup function page.
So what is the solution?
Now that the drawbacks of the Vlookup function have become clear, we can look at some solutions.
The solution that most people use is really more of a workaround. The idea is that you combine the Index and the Match function to each do a part of what Vlookup would normally do: Match can find the value that we’re looking for and return a position and Index can use that position to return a value in a different column. This works and it makes you appreciate how you can solve problems in many ways in Excel. We’ve even created a video explaining this solution.
The thing with this solution is that it makes things more complicated. You’re basically combining two functions to get the same functionality as the one you are replacing. It would be more intuitive to have a single function that can just do a vertical lookup, but also work with values to the left.
The great thing is that this function now exists. In September 2019, Microsoft announced the XLOOKUP function: “The successor to the iconic VLOOKUP function.” This function can do what Vlookup can, but better. And it doesn’t just replace Vlookup, it also replaces its lesser known brother, Hlookup.
Xlookup: the new hotness
Let’s take a look at the Syntax of Xlookup:
XLOOKUP(Lookup value, Lookup array, Return array)Where Lookup value is the value we are looking for. For example, a last name. Lookup array is the array or cell range that contains this value. So that would be the column containing all last names. And finally, Return array is the array or cell range that contains the value we want to return. Which would be the column containing all first names in this example.
By being able to hand-pick what array the lookup takes place in while also being able to choose the array where a value should be returned from, it all becomes much more flexible. Not only does this allow us to look up values to the left, but it also makes for a function that’s more intuitive than Vlookup ever was.
That’s in part due to it using just three arguments. It’s really that easy. Although you can make it more complicated with the optional arguments:
XLOOKUP(Lookup value, Lookup array, Return array, [If not found], [Match mode], [Search mode])The If not found argument allows you to choose a value that should be returned if no match can be found. So that means less tedious #N/A errors.
Match mode allows you to change the way the function matches: 0 (the default) for an exact match, 1 or -1 to match against the nearest smaller or larger item (if there is no exact match), and 2 for a wildcard match. You can learn more about wildcards from the Wildcards section of this post.
Finally, Search mode lets you decide the direction of the search, 1 for first-to-last and -1 for last-to-first. You can also use 2 and -2 for binary searches. If you don’t know what that means, don’t worry, it’s only meant for expert users.
Availability
Really a lot of issues have been solved with this all-new function. But there is one catch: as of writing this, the Xlookup function is not available for everyone yet.
People subscribed to Microsoft 365 (Monthly channel) will have access to it but others still need to wait. It will be available for Semi-Annual Microsoft 365 subscribers from July 2020.
If you’re not using Microsoft 365, but instead are on Excel 2019, Excel 2016, Excel 2013, etc. you will sadly not have access to Xlookup. This may be a good time for you to consider upgrading 😉
We hope you’ve learned something new today. Let us know in the comments how you feel about Vlookup and Xlookup and if you plan on upgrading your workbooks.