560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|Why the XLOOKUP function should kill off VLOOKUP and INDEX/MATCH|
|The XLOOKUP function has at least 7 advantages over an equivalent VLOOKUP function, as this blog shows. Convert to XLOOKUP now, and then convert your colleagues!|
I blogged 12 months ago on the (then new) XLOOKUP and XMATCH functions. With the benefit of hindsight the one which will change Excel users' working lives is the XLOOKUP function, which makes a good claim to replace the VLOOKUP, HLOOKUP, MATCH and INDEX functions in one fell swoop.
For the examples in this blog, I'll use the following list of the top 10 films by (inflation-adjusted) box office takings (you can download the file here):
The workbook includes 5 range names as shown (called Rank, Title, Genre, Gross and Release respectively).
At its most basic, the XLOOKUP function finds the position of an item in a list, and returns something in a different list at the same position:
This function would show the genre corresponding to the film Titanic (as it happens, Drama).
The way this works is that Excel first determines that Titanic is the 3rd item in the Title column, then returns the 3rd item in the Genre column:
This diagram shows why the function returns Genre.
Note that this function would work just as well if the columns of data were rows instead (so the distinction between HLOOKUP and VLOOKUP is no longer important).
Previously in Excel you had to use IFERROR or other complicated functions to test whether a lookup failed - now you can just use the fourth argument to the lookup function:
Mercifully, there isn't a Titanic II (at least, not in this list anyway) so this would return No such film (the value of the fourth argument).
This is another problem which XLOOKUP solves. Previously you would have to either use an array formula or create some complicated solution which relied on numbering columns, but now you can just use dynamic arrays:
We'll create a single array formula entered in the left-hand cell, which spills over into the other two cells to return the takings and release year for any given film.
Here is the formula:
Because D3:F12 (the third argument to the XLOOKUP function, shown shaded in blue above) spans 3 columns, Excel knows to return 3 columns for the results of this formula.
One of the big limitations of the VLOOKUP function was that the thing you were looking up against had to be in the left-most column or top-most row of a block. No longer, as this example shows:
This formula is looking to see where the given film name (in this case, Titanic) appears within the Title range, and returning the value at the same relative position in the Rank range. For this formula the value shown will be 3.
You can find the first item which more or less matches the thing you're looking for using one of the following wildcard characters:
What it represents
Any single character
Any string of characters
For example, the following formula would find the release year for the first film which contains the text end in it:
This is looking up any film with the pattern *end* (note that XLOOKUP - unlike many other text functions in Excel - is not case-sensitive).
You can use the final argument of the XLOOKUP function to search from the top or bottom of a list:
You can either search first to last (the default) or last to first.
Here are the films to remind you of where we're looking:
There are four Drama films: the first one is Gone with the Wind, and the last one is Doctor Zhivago.
Here's how you could find the first or last item:
What it would return
Gone with the Wind
Here's a quick summary to finish of the benefits of using XLOOKUP over either HLOOKUP/VLOOKUP or INDEX/MATCH:
More sensible defaults
By default the XLOOKUP function assumes you're doing an exact match, as is nearly always the case (for the VLOOKUP function this irritatingly wasn't the default).
Any column order
One of the main reasons to use the more complicated INDEX/MATCH over VLOOKUP was that your lookup column didn't have to be the left-hand one in your range. This restriction is now toast!
You can find text which matches a pattern, rather than doing an exact match.
You can look down from the top of a list or up from the bottom.
Rows or columns
You don't have to think whether to use HLOOKUP or VLOOKUP (XLOOKUP works equally well for rows or columns).
An XLOOKUP function can return the whole of a matching row or column, rather than just a single cell.
With XLOOKUP you can tell Excel what to return if no match is found.
In fact, the only reason to keep using HLOOKUP, VLOOKUP or INDEX/MATCH is that everyone else does!
Some other pages relevant to the above blog include:
25 Aytoun Street