Use Xlookup in VBA

You have probably heard about Vlookup and Hlookup functions in Excel. Vlookup is short for Vertical Lookup. With it, we can search for a particular value in our column, to return a value from a different column but in the same row. Hlookup is short for Horizontal Lookup and it does a similar thing, with the difference it searches for a value in a row, not in a column.

With the development of Office 365, a new lookup option was introduced- Xlookup. So far, it is only available in Office 365. Its main advantage is that regardless of where the lookup value is found (in a row or a column) it returns the proper value. In the example below, we will show how the Xlookup works, and how it integrates with Visual Basic for Applications (VBA).

How Xlookup Works

Xlookup can be used to replace both Vlookup and Hlookup. To show the replacement for Vlookup we will first create the table with NBA players and their statistics from several categories (points, rebounds, and assists):

Let us presume now that we want to extract the rebounds for Steph Curry from this table. We will first use Vlookup for this purpose. In cell G1 we will write “Vlookup” and in cell G2 we write Steph Curry. In cell G3 we will write down the following formula:

This is what our formula looks like in the worksheet:

We can use Xlookup for the same purpose. Our formula in cell H3 will do just that. This is what we will write in cell H3:

Cell H2 in the formula represents lookup_value (Steph Curry), range A1:A9 stands for lookup_array (where our value is found), and range C1:C9 refers to return_array (the data that we want to show).

When we insert both of these formulas, we will have the same results- which is number 7:

The usefulness of Xlookup lies in the fact that it can be used to replace Hlookup as well. To show this, we will create the horizontal table with the same data, in which points, rebounds, and assists will be in different rows, not different columns:

Now let us say that we want to extract the number of assists for Kyrie Irving. We will do this by using Hlookup. Our formula will be as follows:

Cell K2 represents our lookup_value (Kyrie Irving), range A13:I16 represents the whole table, while number 4 stands for the row where our data is located in the table (assists). We use FALSE for range_lookup to find the exact value.

This is what our formula looks like in the worksheet:

We can use Xlookup for the same purpose, i.e. to extract the same information. Our formula will be as follows:

Cell L2 is lookup_value (Kyrie Irving), range A13:I13 refers to lookup_array (all players), and range A16:I16 represents return_array (range where our data is located).

This is what this formula looks like in the worksheet:

We will get the same value for Kyrie Irving’s assist number- number 4.

All that is shown above can be used in Visual Basic for Application (VBA) for purpose of automation. We will show how to do that below.

Use Xlookup in Vba

To use VBA, we first need to access it. To do that, we will click on ALT + F11 on our keyboard. On the window that appears, we will right-click on the left window, and go to Insert >> Module:

When we click on this, a new blank window will appear on the right side of the screen. There are two ways to call for Xlookup in VBA. We can use:

• Application.WorksheetFunction.Xlookup
• Application.Xlookup

We will find points for Russell Westbrook (we will put his name in cell A20) and for Paul George (cell A21) using VBA and our created tables. These two cells will be used as lookup values in our formula.

We will first search for Russell Westbrook’s points in our first table. This will be our sub-routine:

First, we position ourselves in cell B20. Then we insert our formula in this cell, using cell A20 as a lookup, range A1:A9 as lookup_array, and range B1:B9 as return_array.

When we run our code by pressing F5 while in the module, we will get the correct result in cell B20:

When comparing values in cell B6 and cell B20, we will see that they are the same.

For the code above, we used Application.Xlookup. As said, Application.WorksheetFunction.XLookup can also be used to achieve the same results.

We will write down the following code to extract Paul George’s points from the second table we have (table with horizontal data):

This formula is pretty similar to the one above, with the difference it imitates Hlookup. We first position ourselves on cell B21, and then we define lookup_value (cell A21- Paul George), lookup_array (range A13:I13– where all the player names are found), and return_array (range A14:I14– where data for points are located).

When we execute the code by pressing F5 on our keyboard (while in the module, and while located in the code with the pointer), this is the result we will get:

We will get the number 33 for Paul George’s points, which is the same number that can be found in row number 14 beneath the name of Paul George, which is a confirmation that the code is good.

The important note is that we must have the lookup_values written in the worksheet before we run the code.

We can also use variables for our code, to show our code more analytically. We will find the points for Kevin Durant in the second table (lookup_value will be in cell A22), and this will be our code:

The code above first declares three variables: player, rngPlayers, and rngPoints. Then we set these variables. The player is cell A22– which is Kevin Durant, rngPlayers is for players in our table, and rngPoints refers to points.

When we execute the code, this is what we will end up with:

This is what our code looks like in the module:

We can also create the code with the Application.InputBox and ask users to input all the values that they want, i.e. to choose all the parameters, but we will not show this in this example.