Microsoft has begun rolling-out Excel’s anticipated XLOOKUP feature to some Office 365 subscribers. While not all Office 365 subscribers yet have access to this feature, if you are an Office 365 subscriber, you can expect receive this feature during 2020. And once you do gain access to it, you will no doubt want to take full advantage of this great new enhancement to Excel.
XLOOKUP is alternative to VLOOKUP, HLOOKUP, and INDEX. While these three functions will remain in Excel, many users will find XLOOKUP to be simpler, more intuitive, and even more powerful. Some of the key differences between XLOOKUP and other lookup functions include:
- XLOOKUP defaults to an exact match, whereas VLOOKUP and HLOOKUP default to an approximate match.
- With XLOOKUP you do not have to specify a column index number as you do with VLOOKUP or a row index number as you do with HLOOKUP.
- The arrangement of columns and rows does not matter with XLOOKUP because it can look to the left or right when using it as alternative to VLOOKUP. Likewise, it can look above or below when using it as an alternative to HLOOKUP.
- XLOOKUP allows you to specify what happens if your lookup value isn’t found, without having to include an IFERROR
An Example
To begin to understand the advantages of working with XLOOKUP, consider the example presented in Figure 1. In this illustration, XLOOKUP is used to find the value from cell H2 in the range of B3 through B15. Keep in mind, this function defaults to an exact match. Once it finds the value it is looking for, it returns the corresponding value from the range D3 through D15. If no match is found, then the formula would return the phrase “Item Not Found.”
Now compare the formula in Figure 2 to the one in Figure 1. See how the values to return are in the column to the left of values in the lookup column. This type of formula would not have been possible with a VLOOKUP function.
Summary
Not all Office 365 subscribers yet have access to XLOOKUP. However, the impending general release of the new feature is creating a lot of conversation. XLOOKUP is considered by most to be a superior option to VLOOKUP, HLOOKUP, and INDEX. Accordingly, as you gain availability to this terrific new function, you will no doubt want to incorporate it into your Excel toolbox.
To learn more about this and other Excel features, consider some of the Excel-focused learning sessions available from K2 Enterprises. For information, visit www.k2e.com/training.
