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, 2021, 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.

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.

Covered in Session 12 – Power Pivot, Data Modelling, OLAP and Business Intelligence

Covered in Lesson 12-20: Use the CUBEVALUE function to query a data model.

Covered in Session 13: An Introduction to DAX

Covered in Lesson 12-6: Use an OLAP pivot table to analysze data residing in a data model.

Covered in Lesson 11-33 Create a merged query using fuzzy logic.

Covered in Session 10: 3D Maps.

Covered in Lesson 11-7: Create and use a custom data type.

Covered in Lesson 9-2: Use Natural Language Queries.

Covered in Lesson 11-8: Use custom data types in formulas.

Covered in Lesson 9-1: Use automatic data analysis to create data insights.

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.

Which tutorial do you need?