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.