This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

Our example workbook
The basic function
What happens if an item isn't found
Returning an entire row or column
The column order no longer matters
Using wildcards
XLOOKUP can look up or down
Summary of the benefits of XLOOKUP

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.

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:

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
=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!

Some other pages relevant to the above blog include:

Training classes in Microsoft Excel

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR