Essential to Expert Skills progression

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”.

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:

excel-xlookup-function-returning-single-value

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

excel-xlookup-function-returning-multiple-values

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.

Learn Excel 365 Expert Skills with The Smart Method (fourth edition) - Front cover

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 (part
two)
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

Related Articles

VLOOKUP inexact match illustration (chicken and egg).

VLOOKUP inexact match

VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.

VLOOKUP using text strings

VLOOKUP using text strings

How to use VLOOKUP to search for a piece of text and return a matching value. This article also explains what VLOOKUP is and when to use it.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Leave a Reply

Your email address will not be published.

5 + nineteen =

Cross-lesson hyperlinks

In order to use all cross-lesson hyperlinks you will need to download the full e-book. The Expert Skills e-book contains 253 short lessons that will teach you everything there is to know about Excel.

When you download the e-book you’ll be able to access every lesson,  online or offline, on all of your devices, using Amazon’s free advanced e-book reader