Locked lesson.

## About this lesson

Advanced options for XLOOKUP including using wildcard matches and returning Dynamic Arrays as an output.

## Exercise files

Download this lesson’s related exercise files.

Wildcard XLOOKUPs and DAs - Begin.xlsx32.3 KB Wildcard XLOOKUPs and DAs - Complete.xlsx

32.8 KB

## Quick reference

### XLOOKUP and Dynamic Arrays

An introduction to the ultimate lookup function: XLOOKUP.

### When to use

When you need to do a VLOOKUP, HLOOKUP, or INDEX/MATCH and have a modern version of Excel

### Instructions

#### Availability

- The XLOOKUP function was released to Microsoft 365 AFTER Excel 2019 was released
- This means that you must have a version of Excel newer than Excel 2019 to use it

#### Advantages over classic lookup formulas

- Defaults to an Exact match (unlike VLOOKUP)
- Data does not need to be sorted (unlike VLOOKUP)
- Data being searched does not need to be in the first column (no need to resort to Index/Match)
- Optimized for performance
- Contains built-in options for alternate results
- Does not rely on hard-coded column positions for matches
- Provides robust match and search modes
- Provides wildcard lookups
- Is compatible with Dynamic Arrays, meaning one lookup can return multiple columns or rows of data

#### XLOOKUP Syntax

- = XLOOKUP(
*lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]*)

#### XLOOKUP Parameters

- lookup_value:
- The value (or cell reference) you wish to look for

- lookup_array:
- The range you wish to look in to find a match
- Can be a range, table row or table column
- Must be a single column or row

- return_array
- The range (or table column/row) you wish to return when a match is found
- To return a single value, provide a range that covers over one column (or row)
- To return multiple values, provide a multi-column (multi-row) range
- To return all columns in case of a match, provide the address of the entire data range or table name

- if_not_found
- Optional, but defaults to #N/A if a match isn’t found
- To provide a text output, wrap it in quotes: “Product not found!”
- To provide a numeric output, it is not necessary to wrap the output in quotes

- match_mode
- Optional, but defaults to an Exact match, unlike VLOOKUP
- 0 Returns an Exact match (or #N/A unless overridden with the “if_not_found” parameter
- -1 Exact match or next smaller item (like VLOOKUP’s approximate match)
- 1 Exact match or next larger item (not possible with VLOOKUP)
- 2 Wildcard match

- search_mode
- Optional, but defaults to search from first to last
- 1 Sorts your data and searches from first item to last until it finds a match
- -1 Sorts your data and searches from last item to first until it finds a match
- 2 Binary search – ascending – searches using VLOOKUP’s sort methods
- -2 Binary search – descending – searches using VLOOKUP’s sort methods, but in reverse order

Lesson notes are only available for subscribers.