BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 24 September 2020
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
A full run-down of the impressive XLOOKUP function
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.
Our example workbook
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).
The basic function
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).
What happens if an item isn't found
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).
Returning an entire row or column
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.
The column order no longer matters
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:
|Character||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).
XLOOKUP can look up or down
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:
|Formula||What it would return|
|=XLOOKUP(GenreName,Genre,Title,,,1)||Gone with the Wind|
Summary of the benefits of XLOOKUP
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!|
|Wildcards||You can find text which matches a pattern, rather than doing an exact match.|
|Variable order||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).|
|Spill formulae||An XLOOKUP function can return the whole of a matching row or column, rather than just a single cell.|
|Showing errors||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!