 Publishers of the world’s most comprehensive and up-to-date Excel tutorials ## Excel tutorials

The only Excel books you will ever need

• The only constantly updated Excel 365 titles more…
• Thoroughly covers Power Pivot and Power Query more…
• Covers business intelligence and OLAP features more…
• Simple, easy, understandable, 2-page lessons more…
• Available as both printed books and e-Books more…
• Used by schools, colleges and universities  more…
• Available for 365, 2019, 2016 and Mac versions more…

# Excel XLOOKUP function (part one) 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”.

## 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. This lesson is excerpted from the above book.

This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.

It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

## Lessons in Session 4

 Session Four: Working with Dynamic Arrays Lesson 4‑1: Understand one-dimensional arrays Lesson 4‑2: Understand two-dimensional arrays Lesson 4‑3: Create a legacy CSE array formula Lesson 4‑4: Understand dynamic array formulas Lesson 4‑5: Understand dynamic array compatibility Lesson 4‑6: Create a drop-down list using a UNIQUE function Lesson 4‑7: Use a FILTER function Lesson 4‑8: Create a searchable drop-down list Lesson 4‑9: Use a SORT function Lesson 4‑10: Use a SORTBY function Lesson 4‑11: Create two linked drop-down lists using dynamic arrays Lesson 4‑12: Use a FILTER function with a custom logic array Lesson 4‑13: Construct Boolean AND/OR logic arrays Lesson 4‑14: Use a FILTER function with AND/OR custom logic arrays Lesson 4‑15: Use a SEQUENCE function Lesson 4‑16: Create a perpetual calendar using the SEQUENCE function Lesson 4‑17: Use a RANDARRAY function Lesson 4‑18: Understand XLOOKUP (part one) Lesson 4‑19: Understand XLOOKUP (parttwo) Lesson 4‑20: Use XLOOKUP to perform a two-dimensional lookup Lesson 4‑21: Use an XMATCH function Lesson 4‑22: Use the SUMPRODUCT function Lesson 4‑23: Use the SUMPRODUCT function with a logic array Lesson 4‑24: Use a SORTBY function with a custom array Session 4: Exercise Session 4: Exercise Answers