Photo by Rui Matayoshi on Unsplash

For those using the VLOOKUP or HLOOKUP functions, here is a helpful replacement for both. XLOOKUP brings many improvements to formulas used to find results within a set of data. These include the ability to find values in columns without regard to their relative position to each other, to handle errors more predictably, to search for partial matches and to return multiple results in a dynamic array.

Usage

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value = The value to look up. Same as with HLOOKUP or VLOOKUP.
lookup_array = The array (or column) to search.
return_array = The array (or columns) from which to return a result.
if_not_found = If no match is found, return this text or value. If no argument supplied, XLOOKUP will return #N/A.
match_mode = Start number or date. Default is 1.
search_mode = 1 – search in ascending order; 2 – search in descending order. Default is 1.

Example 1

All of the examples below are available to download in the XLOOKUP demo workbook. The examples use revenue and expense accounts from York University. In this fictional scenario, the institution has moved from a six to a new seven-digit account numbering system to make room for additional accounts. For staff to use during and after the transition, a tool is required to lookup account numbers. A table has been set up with legacy and new account numbers for this purpose.

With the legacy account number in column A on the far left, it is simple enough to use VLOOKUP to find the new account number in a column to the right. This is the function in B9.

=VLOOKUP(B8,Accounts1[[Legacy Account]:[New Account]],2,FALSE)

However, it is equally important to be able to look up the legacy account number given the new account number. To do this without XLOOKUP, one might use INDEX and MATCH, or set up a duplicate table of accounts but with columns A and B swapped.

With XLOOKUP neither method is necessary. Rather, one specifies the lookup and return columns, or arrays.

=XLOOKUP(B11,Accounts1[New Account],Accounts1[Legacy Account],"No match",0,1)

Further, the formula provides a predictable response in the event that the lookup value is not found.

Example 2

XLOOKUP allows a lookup of a partial match using special characters * and ?.

? represents a single character, while * takes the place of multiple characters.

In the XLOOKUP2 worksheet, the user seeks an account which includes “rate” in the account description. XLOOKUP searches the Account Description column for the first partial match and returns the new account number it finds in New Account column.

=XLOOKUP(B6,Accounts2[Account Description],Accounts2[New Account],"",2,1)

Example 3

Can XLOOKUP return a dynamic array of multiple results?

Yes, and, not yet.

The MultiResult worksheet in the demo workbook includes two demonstrations of returning multiple results. The first one uses XLOOKUP, the second uses FILTER.

In cell B5 the search term with wild card is ‘Advert*’. Below that are two tables of results.

In the first result table in B8:G8, XLOOKUP is used to return multiple columns of data, rather than just the result from a single column. The third argument returns columns B to G, spilling right across the entire result table.

=XLOOKUP(B5,Accounts3[Account Description],Accounts3[[New Account]:[HST Status]],"",2,1)

However, what if there are multiple instances of ‘Advert*’ in the source table?

XLOOKUP can return the first instance, or the last instance by adjusting the sixth argument. But it does not (yet) support the ability to return an array with multiple hits. Hopefully that will be included in a future update to Excel.

Meanwhile, a formula using FILTER with a nested SEARCH provides equivalent results.

=FILTER(Accounts3[[New Account]:[HST Status]], ISNUMBER(SEARCH(B5,Accounts3[Account Description])))

SEARCH returns the starting position of one text string inside another, and supports wildcard searches. In this example, ISNUMBER determines whether SEARCH returns a numeric value or an error. In cases where SEARCH yields a number, that row is included in the FILTER result.

Summary

While HLOOKUP and VLOOKUP will likely be supported in Excel by Microsoft for years to come, there is probably no longer a use case for these that can not be handled better by XLOOKUP.

Download the XLOOKUP demo workbook.

Here are links to the Microsoft documentation for the XLOOKUP, IS and SEARCH functions.