COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
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!

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):

Excel list of films

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:

Looking up a film title

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:

How XLOOKUP works

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:

If not found message

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:

Single array function

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:

The spill 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:

Looking up to left

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.

Using wildcards

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:

Using a wildcard

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:

Sixth argument

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:

Four drama films

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
=XLOOKUP(GenreName,Genre,Title,,,-1) Doctor Zhivago

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:

Benefit Notes
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!

This blog has 0 threads Add post