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.

- Excel 365

- Excel 2019 and earlier

- Last Updated: September 18, 2020

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

- Last updated on September 18, 2020

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

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.

### Excel XLOOKUP function (part two)

This is the second of a two-part series of XLOOKUP lessons. If you haven’t already done so I recommend that you begin with part one

### Using VLOOKUP, if Column 1 is blank, get value from Column 2

This article shows how to create an Excel VLOOKUP formula that extracts data from a different column if the first column it searches is blank.

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