XLOOKUP in Excel: An improved VLOOKUP

A top 10 function

Frequent users of Excel cannot do without the lookup function, particularly VLOOKUP which has been coined by many as one of the top 10 most important things to know in Excel. But VLOOKUP isn’t perfect, and Microsoft has recently created XLOOKUP, a new, more powerful look-up feature that is simpler and without limitations of VLOOKUP.

In comparison

Unlike VLOOKUP (or even HLOOKUP), XLOOKUP can look to its left when looking for values, its default is exact match, and it can specify a range of cells instead of a column number. Essentially, XLOOKUP can do everything VLOOKUP can do, and more:

• It can look up vertically (like VLOOKUP) or horizontally (like HLOOKUP), as well as search from the top down (or left to right) or the bottom up (or right to left).
• Any column/row of the table can be used in the look up code.
• You can specify an entry to return if no match is found
• Characters such as * and ? can be used
• Return a reference to a cell

A simpler syntax

XLOOKUP has five possible arguments:three required arguments and two optional arguments based on the following syntax.

= XLOOKUP ( lookup_value, lookup_array, results_array, [match_mode], [search_mode] )

The required lookup_value argument specifies the value or item for which you’re searching. The required look_up array argument defines the range of cells to be searched, and the return_array argument indicates the range of cells containing the desired value to be returned when Excel finds an exact match.

Note that for the lookup_array and return_array arguments must be of equal (range) length, otherwise Excel will return the #VALUE! error to your formula.

The match_mode determine the type of match that is returned, so if this optional argument is not entered than it results in the XLOOKUP’s exact match default. The search_mode optional argument is the type of search to be performed (default being from top to bottom if not specified).

XLOOKUP

In the following animation, we have a dataset with information about the GDP of different countries. Now image that you want to retrieve the values for one country when you only have the country code. Because Xlookup is a powerful way to retrieve the needed values quickly, we would use the following Xlookup formula:

=XLOOKUP (E4,Client_LifeTime!A:A,ClientLifeTime!E:E,””,,)

The lookup_value is A1, the look_up array is A:A, and the return_array is E:E. Our return value was 0 which left a blank cell.

Using the keyboard shortcut Ctrl + D, the same XLookup formual is applied to D1:D28 to see the return_arrays when applied to other lookup_values.

Continue exploring

Because XLOOKUP is still new, it’s a good idea to practice with it to see how it can assist you in determining specific values that you are looking for. For more lessons on Excel, sign-up at 5miles for a free two-week trial and start one of the 360+ individual trainings.