In the world of finance, “Cash is King”… but remember that data is Emperor!   The ability to process and sort mass data, picking out required information can be a burden. From analysis expenses and revenues to market trends, financial professionals rely heavily on spreadsheets but often do so with ‘self-taught’ inefficient knowledge and tools.

Microsoft Excel is the go-to tool for this purpose, and knowing some fundamental data-retrieval functions is as absolute must-have. It’s suite of Lookup functions are probably the most widely used. Although other Excel functions and tools are available, the Lookups are an invaluable asset for anyone working with large datasets. Two of the most widely used lookup functions are VLOOKUP (its twin sibling the HLOOKUP too) and the newer XLOOKUP.

Lookup functions in Excel are designed to retrieve specific data from a table or range based on a set of criteria. They are particularly useful when you need to find and extract information from a large dataset without manually sifting through it. At Capital City Training, we show delegates how LOOKUPs can also be used to pick out alternative scenario assumptions data too, adding a lot of flexibility to a financial model.

Article Contents

• What are the Lookup Functions?
• VLOOKUP and HLOOKUP
• XLOOKUP
• What’s the difference between an XLOOKUP and a VLOOKUP?
• Error Handling and Troubleshooting
• Advanced Techniques and Workarounds
• Lookup with Multiple Criteria:
• Examples of VLOOKUP and XLOOKUP Use Cases in Finance
• Case Sensitivity and Cross-Worksheet Lookups
• Best Practices for Data Organization

What are the Lookup Functions?

VLOOKUP and XLOOKUP are two of the most commonly used lookup functions in Excel. While VLOOKUP has been a staple for many years, XLOOKUP is a newer addition that offers enhanced functionality and flexibility. It’s important to note that XLOOKUP is only available in Excel 365 and later versions, so users with older versions of Excel may need to rely on VLOOKUP or other alternatives.

VLOOKUP and HLOOKUP

VLOOKUP is a vertical lookup function, meaning it searches for a value in the first column of a table or range and returns a corresponding value from the same row in another column. HLOOKUP, on the other hand, is a horizontal lookup function that searches for a value in the first row of a table or range and returns a corresponding value from the same column in another row.

XLOOKUP

XLOOKUP is a more versatile and powerful lookup function introduced in Excel 2021. Unlike VLOOKUP, which searches vertically, XLOOKUP can search both vertically and horizontally, making it more flexible and efficient. Additionally, XLOOKUP offers advanced options like inexact matching and the ability to search in a specific column or row.

What’s the difference between an XLOOKUP and a VLOOKUP?

I think the simplest thing to do here is a demonstration. We will show you the ropes, but we also have a downloadable version you can play along with at your desk.

VLOOKUP Illustration

Let’s take the simple model below where there are 5 alternative Revenue scenarios for a business. The user will enter the desired scenario in cell C7, and we will use the LOOKUPS to retrieve the relevant Revenue into rows 23.

The correct formulation for a VLOOKUP function needs 4 elements, as prompted below when you type in “=VLOOKUP(“

1. Lookup_value: the variable (scenario in this case) you wish Excel to Lookup in the data array.

Here it is $C$7, where the user has selected the scenario they wish to see. Dollarised as the lookup value is only ever in cell C7.

2. Table_array: the complete table of data where Excel will find the lookup_value in the first column. VLOOKUP cannot work any other way – Excel will only search for the value in the first column of the array.

Here, it is $B$13:$J17. Dollarised as the cell references for the array don’t change.

3. Col_index_num: Using the first two elements of the coding, above, Excel will have identified the relevant row of data by matching the lookup_value to the array. So now we just need to tell Excel which column of data we wish to retrieve. We have inserted the column numbers in red above, so you can clearly see them, but all we need here is the cell reference.

Here, it is C$11. The C is not dollarised as we want the column reference to move across the page as we copy the formula across row 23.

4. [range_lookup]: note that this part of the formula spec is in square brackets, which means it is optional. Beware! Never leave it out. If you do you may get spurious results.

Here, we will enter “FALSE”.  This tells Excel that we want it to find an exact match for the lookup value in the array. If you do not select FALSE, you will only get the correct answer if the first column of the lookup array is in alphanumeric order. Try it!

So here’s the final result:

Enter this formula, copy it across the time series to 2027 and see you get the respective scenario data. Change the scenario in C7 and watch the Revenue line change.

XLOOKUP Illustration

The same result can be achieved with the XLOOKUP shown below:

We still enter firstly the lookup_value, but then the lookup_array is just the column of cells containing the scenario names (B13:B17) and we then enter the return_array which is the column of cells containing the revenue data for 2024. Do no dollarize the return array, and when the copy the formula across the time series, the return array will follow the formula. So there is no need to specify a column_reference as with Vlookup. Much more versatile!

The primary difference between XLOOKUP and VLOOKUP lies in their functionality and flexibility.

VLOOKUP:

• Searches vertically (down columns)
• Requires the lookup column to be the first column in the range/table
• Can only perform exact matches (unless combined with other functions)
• Limited to returning a single result

XLOOKUP:

• Searches can search both vertically and horizontally
• Allows you to specify the lookup column and result column
• Can perform exact and inexact (wildcard) matches
• Can return multiple results using an array format

So, which one is the best? While VLOOKUP has been a reliable workhorse for many years, XLOOKUP is generally considered the superior option due to its enhanced capabilities and flexibility. However, the choice between the two often comes down to personal preference, familiarity, and the specific requirements of the task at hand.

 

When would you use a VLOOKUP or XLOOKUP?

VLOOKUP is still a viable option for simple vertical lookups, especially if you’re working with legacy spreadsheets or collaborating with colleagues who are more familiar with this function. It’s also a good choice when your data is already structured in a way that aligns with VLOOKUP’s requirements.

XLOOKUP, on the other hand, is the preferred choice for more complex lookups, especially when you need to search horizontally or perform inexact matches. It’s also useful when you need to return multiple results or when the data is not structured in a way that aligns with VLOOKUP’s limitations.

Error Handling and Troubleshooting

While lookup functions are powerful tools, they can sometimes produce errors or unexpected results. Here are some common errors you may encounter and how to troubleshoot them:

• #N/A error: This error occurs when VLOOKUP or XLOOKUP cannot find a match in the lookup range. To fix this, double-check your data for any spelling mistakes or inconsistencies, and ensure that the lookup value exists in the appropriate column or row.
• #VALUE! error: This error can occur if the function arguments are not in the correct format or order. Carefully review the syntax and ensure that you are providing the correct type of data for each argument.
• Incorrect results: If the lookup function is returning incorrect or unexpected results, verify that the lookup range and column/row references are correct. Also, check if the data in the lookup range has been sorted or filtered, as this can affect the lookup process.

To troubleshoot lookup function issues, you can use Excel’s built-in error checking tools, such as the Formula Evaluator or the Watch Window. Additionally, breaking down complex formulas into smaller components can help isolate the problem.

Advanced Techniques and Workarounds

Nested Lookup Functions:

You can nest one lookup function inside another to perform more complex lookups. For instance, you could use XLOOKUP to retrieve a value from one table, and then use that value as the lookup value in a second XLOOKUP or VLOOKUP to retrieve data from another table.

INDEX/MATCH Workaround:

If you’re working with older versions of Excel that don’t support XLOOKUP, you can use the INDEX and MATCH functions together to achieve similar functionality. The INDEX function returns a value from a range based on row and column numbers, while MATCH finds the position of a value in a range.

OFFSET Function:

The OFFSET function can be used in conjunction with lookup functions to create dynamic ranges that adjust based on certain criteria. This can be useful when working with data that changes frequently or when you need to perform lookups across multiple sheets or workbooks.

Wildcard Matching:

While VLOOKUP can only perform exact matches, you can use the combination of XLOOKUP and wildcard characters (e.g., * or ?) to perform partial or inexact matches. This can be particularly useful when dealing with incomplete or imperfect data.

Lookup with Multiple Criteria:

By combining lookup functions with other Excel functions like AND, OR, and IF, you can create formulas that perform lookups based on multiple criteria simultaneously.

These advanced techniques and workarounds demonstrate the versatility and power of Excel’s lookup functions, especially when combined with other functions and features. However, it’s important to remember that as formulas become more complex, they can also become more difficult to read and maintain, so it’s crucial to strike a balance between functionality and readability.

 

Examples of VLOOKUP and XLOOKUP Use Cases in Finance

In the finance industry, lookup functions are invaluable for a variety of tasks, such as:

Retrieving financial data:

Suppose you have a large dataset containing stock prices, company financials, or economic indicators. You can use VLOOKUP or XLOOKUP to quickly find and extract specific information based on a company name, ticker symbol, or date range.

Generating reports:

Financial reports often require pulling data from multiple sources and presenting it in a structured format. Lookup functions can help you consolidate and organize this data efficiently.

Expense tracking:

When managing expenses for a company or department, you can use lookup functions to categorize and analyse expenditures based on criteria such as department, project, or expense type.

Portfolio analysis:

Investment professionals can use lookup functions to analyse portfolio performance, calculate returns, and compare investments based on various metrics.

Tax calculations:

In the realm of taxation, lookup functions can be used to retrieve tax rates, deductions, or other relevant information based on income levels, filing status, or other criteria.

Risk analysis:

Financial institutions often use lookup functions to assess risk factors, calculate credit scores, and evaluate lending decisions based on various criteria.

These are just a few examples of how VLOOKUP and XLOOKUP can be leveraged in finance. As you can see, these functions are indispensable tools for anyone dealing with large datasets and complex calculations in the financial world.

Case Sensitivity and Cross-Worksheet Lookups

When working with lookup functions, it’s important to consider case sensitivity and the ability to perform lookups across multiple worksheets or workbooks.

Case Sensitivity:

By default, lookup functions in Excel are case-insensitive, meaning they treat uppercase and lowercase letters as the same when searching for a match. However, if you need to perform a case-sensitive lookup, you can use the EXACT function in combination with your lookup function to ensure that the case matches exactly.

Cross-Worksheet and Cross-Workbook Lookups:

Lookup functions can work with data across multiple worksheets or even different workbooks, as long as the workbooks are open and the references are properly formatted. To perform a lookup across worksheets, simply include the worksheet name followed by an exclamation point before the cell or range reference (e.g., ‘Sheet2’!A1:B10). For cross-workbook lookups, include the workbook name in square brackets before the worksheet reference (e.g., ‘[Workbook1.xlsx]Sheet1’!A1:B10).

Best Practices for Data Organization

To optimize the performance and accuracy of lookup functions, it’s crucial to organize your data in a structured and consistent manner. Here are some best practices to follow:

• Keep data in a tabular format – Arrange your data in a table-like structure with clearly defined rows and columns. This makes it easier for lookup functions to find and retrieve the desired information.
• Use unique identifiers – Ensure that the column or row you’re using as the lookup reference contains unique values. This prevents ambiguity and ensures that the lookup function returns the correct result.
• Avoid blank cells – Blank cells in your data can cause issues with lookup functions, leading to errors or incorrect results. Consider using placeholder values or filtering out blank cells before performing lookups.
• Maintain data consistency – Ensure that the data in your lookup column or row is consistent in terms of formatting, spelling, and case. Inconsistencies can prevent lookup functions from finding the correct match.
• Use named ranges – Defining named ranges for your lookup tables can make formulas more readable and easier to maintain. Instead of using cell references, you can simply refer to the named range in your lookup function.

By following these best practices, you can streamline your data organization and ensure that your lookup functions work efficiently and accurately.

In conclusion, while VLOOKUP has been a stalwart in Excel for years, the introduction of XLOOKUP has opened up new possibilities for more efficient and flexible data retrieval. Whether you’re dealing with financial reports, investment analysis, or any other data-driven task, mastering these lookup functions can greatly streamline your workflows and enhance your productivity.