Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
The XLOOKUP Function 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:
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 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.
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:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.