Excel XLOOKUP function (part one)

February 12, 2022
No Comments

The new Excel XLOOKUP function was introduced in the July 2020 Excel 365 semi-annual update. It isn’t available in older versions (Excel 2019 and earlier). The July 2020 update was a huge leap forward for Excel as you are now able to work with dynamic array functions that return more than one value.

In preparing the fourth edition of Expert Skills I had to add a whole new session to simply explain Dynamic Arrays from first principles and then to introduce several new dynamic array functions. You can see the Dynamic Array session outline in the right-hand sidebar below. This lesson is excerpted from the Dynamic Array session in the fourth edition of Expert Skills. The powerful new XLOOKUP function entirely replaces the old VLOOKUP function and, thanks to the new dynamic array capability, makes many tasks a lot easier.

I’ve published lessons 18 and 19 (from the dynamic array session in the book) online. When you’ve read this lesson move on to the next (part two) lesson.

I hope you find them interesting.

note

The Excel XLOOKUP function will not work in legacy Excel versions (Excel 2019 and earlier)

The Excel XLOOKUP function was added in the Jul 2020 semi-annual version 2002.

If you must share your workbooks with users of legacy versions of Excel (Excel 2019 and earlier) you will not be able to make use of this feature.

You can expect the deprecated VLOOKUP and HLOOKUP functions to continue to be supported in future Excel versions.

Microsoft have stated:

“VLOOKUP and HLOOKUP will both continue to be supported by Excel. That said, we strongly recommend using XLOOKUP in favor of VLOOKUP and HLOOKUP”.

Lesson 4 18: Excel XLOOKUP function (part one)

You should use the Excel XLOOKUP function and not VLOOKUP, HLOOKUP or INDEX/MATCH in new work

The Excel XLOOKUP function was introduced in the Jul 2020 semi-annual version 2002. This was a landmark release as it added support for dynamic arrays.

You learned to use VLOOKUP and HLOOKUP in: Lesson 3 25: Use a VLOOKUP function for an inexact lookup.

You learned to use INDEX and MATCH together to emulate a VLOOKUP in: Lesson 3 27: Use the INDEX function.

Unless backward compatibility with legacy Excel versions (Excel 2019 and earlier) is required, you should use XLOOKUP for new work. As XLOOKUP is far more versatile and is easier to use.

Later, in: Lesson 4 21: Use an XMATCH function, you’ll learn about the new XMATCH function, the modern replacement for the old MATCH function.

Excel XLOOKUP function simple example

Here is the Excel XLOOKUP function syntax:

=XLOOKUP (lookup, lookup_array, return_array,
[not_found], [match_mode], [search_mode])

The first three arguments (those without square brackets) are required and the last three (those inside square brackets) are optional.

The Excel XLOOKUP function can return a single value or an array of values. Here’s how it works:

You can see that XLOOKUP requires a lookup value and two arrays. In this example the Lookup value is “Apples”.
The lookup_array contains:

{ “Pears”; “Apples”; “Bananas”}

and the return_array contains:

{22;33;50}.

XLOOKUP searches for the lookup value Apples in the lookup_array. When the value is found XLOOKUP remembers that Apples is the second element in the lookup_array.

XLOOKUP then fetches the value contained in the second element in the return_array (33) and returns it to the cell containing the function (cell F4).

Of course, you could have done almost the same thing with the old VLOOKUP function but, as you’ll see in a moment, re-designing VLOOKUP as an array function provides many advantages.

The Excel XLOOKUP function can use any column for the lookup and return arrays

In this example you can see that XLOOKUP has achieved something that isn’t possible with VLOOKUP. You’d have had to use an INDEX/MATCH combination in legacy excel to achieve this.

If you’ve read this far, move on to the second lesson in this, series of two XLOOKUP lessons.