In September 2019, Microsoft introduced a new function to help speed up the way that data lookups are performed. As of February 2020, the XLOOKUP in Excel function is still in testing; however, upon release, it will change the way that lookups are done in Excel!
Overview of XLOOKUP in Excel
The XLOOKUP function replaces a number of other functions frequently used when using the VLOOKUP or INDEX / MATCH functions.
However; the data required for an XLOOKUP is the same as what is required for a VLOOKUP.
The required arguments are:
- A lookup value provides the term that the lookup is trying to find; this will normally be text or a unique value (like an account number).
- The lookup array provides the list of values that the XLOOKUP function is looking for. In a VLOOKUP function, this is the first column. In an HLOOKUP function, this is the first row.
- The return array provides the list of values where the match is located for the XLOOKUP function. In the case of a VLOOKUP, this would be the column number from the left that the VLOOKUP is trying to find.
The Sample Data
The following is a very simple chart of accounts with 6 months of data. This will be used to demonstrate some of the capabilities of the XLOOKUP function.
This data contains 6 months of fictional results for 3 asset accounts, along with their account numbers. It is common to use VLOOKUP to acquire results from similar kinds of tables.
Replacing the existing LOOKUP functions
The XLOOKUP function can replace all existing LOOKUP functions, including VLOOKUP, HLOOKUP as well as INDEX / MATCH.
Replacing VLOOKUP with XLOOKUP
In this example, we will be performing a lookup to get the Accounts Receivable balance for the month of April.
To perform a VLOOKUP, you select a cell containing the words Accounts Receivable (in cell B8). Next, select the table containing the data, making sure that the first column is the account names. April is the 5th column in the table, and finally add a 0 (or FALSE) for an exact match.
The XLOOKUP example is similar. The lookup value is the same, but instead of adding the entire table of values, you only require the column containing the values to look up. Next, add the column containing the values that are being looked up. There is no longer a need to add a 0 after the lookup column!
Replacing HLOOKUP with XLOOKUP
In this example, we will be performing a lookup to get the Cash balance in March; however, in this case we will be using HLOOKUP to look up the month name instead of the account name.
To perform an HLOOKUP, you select a cell containing the word March (in cell C13). Next, select the table containing all the monthly data, making sure to select the month names. Cash is the second row in the table, and finally add a 0 (or FALSE) for an exact match.
To create an XLOOKUP, use the same word as with the HLOOKUP (in cell C14). Next, select the row that contains the month names. Finally, select the row containing the Cash account (row 2). Like with VLOOKUP, there is no need to add a zero!
Replacing an INDEX / MATCH with XLOOKUP
Until XLOOKUP, the only way to get a value to the left of a column that you were looking for was to use a combination of the INDEX and MATCH functions. This functionality is described in another post on this site.
In this example, we will need to get the account number for the Inventory account; unfortunately, it is to the left of the word Inventory. Due to it being to the left of the lookup, the VLOOKUP function cannot be used.
As a result, first the word “Inventory” needs to be matched, then indexed in the account numbers column.
Unlike VLOOKUP, the XLOOKUP function can pick any column; even if it is to the left of the existing value! Simply select the word to look for, the column
Comparing LOOKUPs and XLOOKUP
| VLOOKUP / HLOOKUP | XLOOKUP | |
| Value to Find | A value or cell containing a value. | A value or cell containing a value. |
| Location to lookup data | A full table containing data. | A row or column containing the values to look up. |
| Location to find data | A number of rows or columns offset from the lookup. | A specified row or column containing the values to find. |
| Exact Match | A zero or FALSE is required, or it will default to closest match. | Exact match is selected by default. |
| Available | In all versions of Excel. | In Office 365 only. |
Next Steps
The examples provided showed simple ways that the XLOOKUP in Excel function can replace existing lookup functions.
There are many new abilities that XLOOKUP has that VLOOKUP and INDEX / MATCH did not have, including:
- Default values for no match.
- Bottom to top lookup.
- Getting multiple matches from a single function.