ML with circles

Mearns Learns

XLOOKUP

The XLOOKUP function searches an array or range, and returns the item(s) corresponding to the first match it finds.
If no match exists, then XLOOKUP returns a #N/A .

Syntax

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

The required arguments in english, ignoring the optional arguments in square brackets.

=XLOOKUP(What you want to find, The list to search, The list of values to return)    

The simplified rules:

  1. The lookup_value is usually a single value or cell, it can be a range.

  2. The lookup_array and return_array must have the same number of rows.

The required arguments are as follows.

ArgumentDescriptionTypeNotes/values
lookup_valueThe value to lookupRequiredIf omitted, a blank cell is used for matching. Note: An empty string is not the same as a blank cell, it is not valid as a lookup_value, and will produce an error.
lookup_arrayThe array or range to search for the lookup_valueRequiredThe number of rows must match the return_array
return_arrayThe array or range to return when matched to the lookup_valueRequiredThe number of rows must match the lookup_array

The following arguments are optional.

ArgumentDescriptionTypeNotes/values
[if_not_found]Where a valid match is not found, the [if_not_found] text is returned.OptionalIf a valid match is not found, and the [if_not_found] setting is not used, #N/A is returned.
[match_mode]Specify the match type to be usedOptional0 - Exact match. If nothing is found, return #N/A. This is the default.
-1 - Exact match. If nothing is found, return the next smaller item.
1 - Exact match. If nothing is found, return the next larger item.
2 - A wildcard match using *, ?, and ~ characters.
[search_mode]Specify the search mode to be usedOptional1 - Perform a search starting with the first item. This is the default.
-1 - Perform a reverse search starting with the last item.
2 - Perform a binary search. It relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
-2 - Perform a binary search. It relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

Comments

XLOOKUP was introduced to Office 365 in July 2020.

It acts as a modern and flexible replacement for older functions like:

  • LOOKUP
  • VLOOKUP
  • HLOOKUP
  • INDEX and MATCH

Using XLOOKUP has multiple benefits.

  1. Easier syntax than VLOOKUP.
  2. Defaults to an exact match.
  3. Inserting columns in the source data does not cause invalid results to be returned.
  4. Can search and return values to the left.
  5. Vertical and horizontal lookups.
  6. Built in error handling for no results.
  7. Returns a range instead of a value.
  8. Can be used to do a two way match.

Best to use

  • In most situations, use the XLOOKUP function instead of VLOOKUP, HLOOKUP, LOOKUP, INDEX AND MATCH.
  • Combining a vertical and horizontal XLOOKUP to return an intersection point is very powerful use of the function.

Limitations

The function will always return the first match but can be made to return a range linked to the first match.

Tip

If the XLOOKUP function is not matching to data but the lookup_value appears in the lookup_array, check these things.

  • The lookup_value and lookup_array need to be the same data format. A text lookup_value will not be matched to a numeric value in the lookup_array.
  • Check that the lookup_value and lookup_array do not have mismatched leading or trailing spaces or non-printable characters. The TRIM and CLEAN functions can be used to clean up the data.

Examples

The simplest use of XLOOKUP, doing a lookup on arrays.

XLOOKUP example XLOOKUP example

The usual use of XLOOKUP, doing a lookup on ranges.

XLOOKUP example XLOOKUP example