XLOOKUP Function

What is the XLOOKUP Function in Excel?

The XLOOKUP Function[1] in Microsoft Excel allows us to look for specific entries in a large array of data. The function requires 3 inputs, the key which is the value you are looking for, the array of values to look for the key.

Microsoft introduced the XLOOKUP function in a 2019 update, where it was described as a successor to Excel’s VLOOKUP and HLOOKUP functions.

The VLOOKUP function is one of the most popular Excel functions and is widely used in financial modeling. XLOOKUP is meant to address some of VLOOKUP’s limitations such as the inability to search to the left of the lookup array.

What is XLOOKUP?

XLOOKUP is similar to VLOOKUP, except that it allows us to search for the data anywhere in the data array and not just search for data to the right of the lookup array. In addition, XLOOKUP allows us to search for arrays of data (VLOOKUP only allows us to search for single cells). The XLOOKUP function comes with the following syntax:

=XLOOKUP(lookup_value,lookup_array,return_array)

What is VLOOKUP?

VLOOKUP allows us to look for specific entries in an array of data. The syntax for the VLOOKUP function is as follows:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

VLOOKUP searches for the lookup value in the first column of the table array. Once it finds the lookup value, it returns the value that is in the same row as the lookup value but in the “col_index_num” column of the array. Consider the following example: =VLOOKUP(Charlie, A1:C4, 3,FALSE)

In our example, VLOOKUP searches for Charlie in the first column of the table array (A2:C5). Once it finds Charlie, it looks for the cell that is in the same row as Charlie (i.e., row 4) and in the third column of the array (i.e., column C). A range lookup value of FALSE means that Excel will only return an exact match.

Illustrative Example – VLOOKUP vs. XLOOKUP vs. INDEX/MATCH

A financial analyst receives a spreadsheet that contains the US dollar exchange rate of various countries. The spreadsheet is quite large, and the analyst wants to find a quick way of navigating the spreadsheet and finding the relevant exchange rate. The spreadsheet is shown below:

Below, VLOOKUP, XLOOKUP, and INDEX/MATCH all give the same result.

VLOOKUP Limitation and the Need for XLOOKUP – Illustrative Example

A second analyst wants to do the same thing. However, the analyst is given a different spreadsheet, which is shown below. The analyst can’t delete either the currency code or the relative strength of the currency because they are essential for future analyses.

Here, VLOOKUP fails but XLOOKUP and INDEX/MATCH both give the correct result.

INDEX/MATCH Limitation and the Need for XLOOKUP – Illustrative Example

A third analyst is given the same spreadsheet as the second analyst. However, the analyst wants to find both the dollar exchange rate and its inverse. The analyst can simply use XLOOKUP or INDEX/MATCH to first find the dollar exchange rate and then directly find its inverse. Since the spreadsheet already contains the inverse of the dollar exchange rate, the analyst can use XLOOKUP to find both. The process is less messy syntax-wise and also decreases the amount of memory needed by Excel.

More Resources

CFI is the official provider of the global Financial Modeling & Valuation Analyst (FMVA)™ certification program, designed to help anyone become a world-class financial analyst. To keep advancing your career, the additional resources below will be useful:

• Advanced Excel Formulas
• LOOKUP vs. VLOOKUP
• Types of Financial Models
• SUMIFS Function in Excel
• See all Excel resources