In my last post, I detailed how to replace existing VLOOKUP and other lookup formulas with XLOOKUP. However; XLOOKUP is more than just a replacement to the VLOOKUP and INDEX / MATCH functions!
XLOOKUP adds some additional functionality that was not available in VLOOKUP. Some of these additional functions include:
- Adding a default value in case the lookup does not provide a match.
- Looking from the bottom results to the top results.
- Partial (wildcard) matching.
- Returning more than one result.
- Looking up based on multiple criteria.
XLOOKUP abilities are increasing with every new release of Office 365, so there may be even more functionality added in the future!
Adding a default value if the lookup does not find a match.
For this example, we will be using a very basic chart of accounts that we used in my previous post. This chart has 3 accounts, and data is provided on a monthly basis for 6 months.
If you were to use the VLOOKUP function to find the account name for an account that exists, it would work as expected.
However, if it was an account that we did not know about, it would cause an #N/A if we were to use the VLOOKUP formula.
To make the formula work properly with VLOOKUP, we need to add an additional IFNA function.
However; with XLOOKUP, we can add an additional argument to the formula to provide a result in case the lookup does not provide a match. This reduces the complexity of the formula, making it easier to read!
Data for Future Examples
Since the next set of examples are more complex, the data set that will be used is a mock version of an apartment rent roll. This data is stored in a table on the RentRoll tab in the spreadsheet linked below.
A rent roll is used to determine the tenants in a building, and the amount they owe on a monthly basis. This example is for February 2020.
Looking from Bottom to Top
Let’s say that we want to find out who the tenants are in our units. Using a VLOOKUP function on the unit number, we can quickly find out who the tenant is in our unit.
But wait! When we get further down the list, it returns Karen S Scott as the tenant in Unit 105.
If we look at the data, it says that Karen S Scott moved out in January 2020, and the unit is now occupied by Robert P Bower.
The only way that we can resolve this issue (without resorting to very complex formulas) is to re-sort the list.
Fortunately, XLOOKUP provides a better way to get the most recent name! By adding a -1 as the final criteria (this is after 3 commas, unless there are other criteria), the XLOOKUP looks from the bottom to the top.
Notice that the lookup is able to provide the current tenant, Robert P Bower!
Partial / Wildcard Matching
VLOOKUP has some capability to perform partial matches, but the functionality is quite unreliable.
In this example, we try to look up the tenant Mary J. Pang’s unit type; however, we will not enter her last name to see if VLOOKUP’s approximate match will work.
In this case the VLOOKUP approximate match was unsuccessful, as Mary J. Pang is in a 1 bedroom apartment.
With XLOOKUP, there are additional options on how the formula should handle cases where it cannot find an exact match (the default). You can now check for the next highest, next lowest, or insert a “*” as a wildcard character to do a partial match.
When we first try our wildcard match, it shows that it has not found any match – this is because we didn’t add the wildcard into the formula.
Since there was no asterisk in the initial formula, we add a wild card by adding a & “*” after the term that we are searching for. After doing this, it correctly shows that Mary J. Pang is in a 1 bedroom apartment.
Using wildcards, it is also possible to search from anywhere within the text; in this case, you can search based on last name.
Returning More than One Result
In the next example, we will be investigating a case where multiple results need to be returned from our rent roll table.
Traditionally, with VLOOKUP, it would involve creating a different formula for each possible match. The formula below would need to be repeated for name, type and square feet.
With XLOOKUP, we can specify multiple columns, so that there is no need for the formula to be entered more than once.
Clicking on a cell other than the one containing the XLOOKUP formula will show greyed out values. This means that the formula has not been entered into this cell.
It is also possible to take another function such as SUM to aggregate all the results that have been provided by an XLOOKUP.
XLOOKUP will still only return the first match that it finds; however, without doing more complex formulas.
Looking up based on Multiple Criteria
Until XLOOKUP, it was impossible to use a single formula in order to provide results based on multiple criteria.
In this example, we will use both the building name and unit to find the tenant name. While there were workarounds using VLOOKUP, they generally involved creating helper columns (a column that concatenated multiple columns).
Alternatively, there was the INDEX / MATCH array formula. While this formula worked, it could be very slow, especially with large data sets. This formula was also hard to understand for non-advanced users.
With XLOOKUP, there is no need to create complex array formulas for matching against multiple criteria – it can be done by simply concatenating the two columns together for the lookup.
Conclusion – XLOOKUP New Functionality
The XLOOKUP function is a major change to the way lookups are done; the VLOOKUP formula was one that has existed since the first version of Excel!
Some of the new functionality, such as bottom to top lookup, will become an invaluable tool for those who frequently use lookup formulas.
Other functions may be less used, as the functionality may be more likely to be used in pivot tables or cube formulas.
By reducing reliance on multi-function solutions, such as INDEX / MATCH() or ISNA(VLOOKUP()), the XLOOKUP formula acheives the objective of making lookup formulas more accessible to a broader number of users.