Microsoft Excel Tips and Tricks - Computergaga

XLOOKUP Function in Excel

The XLOOKUP function in Excel is a complete lookup function that blends many attributes of other lookup functions into one super function.

It was released in 2019 and is only available in Excel 365, Excel Online and the Excel 2021 versions.

It is viewed by many as the replacement to the VLOOKUP and HLOOKUP functions in Excel. This is not strictly the case, but it does have many advantages when compared to VLOOKUP.

Some of the key advantages of the XLOOKUP function in Excel include;

  • The function is very flexible. It can lookup values down a column, or across a row. And, can also return values from a column to its left (VLOOKUP only returns from columns to the right).
  • The lookup column and return column can be specified with a range or table column reference. It doesn’t require the columns index number like VLOOKUP.
  • XLOOKUP defaults to an exact match (the most popular lookup type).
  • It can return a value, or a range. Its ability to a return a range is fantastic. VLOOKUP can only return a single value.
  • It contains a built-in error handling argument to prevent the return of #N/A errors.

XLOOKUP Function Anatomy

The XLOOKUP function has six arguments. This is a testimony to its many attributes. However, for a typical lookup, only the first three arguments are required.

The arguments of the XLOOKUP function in Excel.
  • Lookup value: The value you are looking for.
  • Lookup array: The range or array that you are looking for the lookup value in. This can be a column or a row.
  • Return array: The range or array that contains the value or range to return.
  • If not found: The action to perform if the lookup value is not found. This is an optional argument.
  • Match mode: How you want to match the lookup value. The options include an exact match, two types of range lookup and the use of wildcards. This is an optional argument. The default value is 0, which specifies an exact match.
  • Search mode: How you would like to search for the lookup value. You can search first-to-last, or last-to-first. This argument is optional. The default value is 1, which specifies to search from first-to-last.

Download the sample Excel workbook to follow along with the XLOOKUP function examples.

Exact Match Lookup with XLOOKUP

An exact match is the most popular type of lookup in Excel. The XLOOKUP function defaults to this lookup type, making it very quick and simple to perform.

In the following image we have a table named tblCodes that lists country names and their three-digit country code.

Table with country names and codes

The following XLOOKUP function is used to look up and return the countries code into a customers table.

=XLOOKUP([@Country],tblCodes[Country],tblCodes[Code])
Exact match lookup with XLOOKUP in Excel

You can see that only the first three arguments were used.

This formula uses table references for its lookup value and the lookup and return arrays. It is not required that your data is formatted as a table, but tables and XLOOKUP work brilliantly together. Fast and descriptive references.

When compared to VLOOKUP, in this formula we were able to specify the lookup column, which could have been anywhere in the table, and the return column instead of a column index number required by VLOOKUP.

Handling #N/A Errors

If a country name is missing or input incorrectly, then XLOOKUP will return the #N/A error just like the MATCH and VLOOKUP functions do.

XLOOKUP returning an error due to a missing value

However, XLOOKUP has a built-in argument to handle these errors.

The following update to the previous formula uses the if not found argument to display an empty string instead of the #N/A error.

 =XLOOKUP([@Country],tblCodes[Country],tblCodes[Code],"")
If not found argument of the XLOOKUP function in Excel

Range Lookup with XLOOKUP in Excel

The other type of lookup in Excel, is the range lookup. Although not as commonly used as the exact match lookup, this can be very useful.

Now, because XLOOKUP defaults to exact match, we will need to specify the use of the range lookup via one of its extra arguments.

In the following image, we have a table named tblDiscounts that contains the discount percentage earned by the amount spent by a customer.

Lookup table of discounts

The following XLOOKUP function in Excel, returns the discount based on the amount spent.

=XLOOKUP([@Amount],tblDiscounts[Amount],tblDiscounts[Discount],,-1)
Range lookup with the XLOOKUP function

The fifth argument, match mode, is used to specify the range lookup. There are four options in this argument. Number -1 specifies to return the next item smaller than.

Options for the match mode argument

The if not found argument has been skipped in this example for demonstration purposes.

A strength of XLOOKUP is that it does not require the column of value ranges to be in order to work. VLOOKUP and MATCH require this. Now, it is unlikely that a column such as this would not be in ascending order to be fair, however it does mean XLOOKUP is more robust.

More Examples of the XLOOKUP Function in Excel

The following video shows further examples of the XLOOKUP function in Excel including a multiple column lookup, wildcard matches, and using XLOOKUP to return a range to another Excel function.