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:

1 |
=VLOOKUP(G2,A1:D9,3,0) |

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**:

1 |
=XLOOKUP(H2,A1:A9,C1:C9) |

**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:

1 |
=HLOOKUP(K2,A13:I16,4,FALSE) |

**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:

1 |
=XLOOKUP(L2,A13:I13,A16:I16) |

**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:

1 2 3 4 |
Sub Westbrook_Points() Range("B20").Select ActiveCell = Application.XLookup(Range("A20"), Range("A1:A9"), Range("B1:B9")) End Sub |

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):

Sub George_Points()

Range(“B21”).Select

ActiveCell = Application.WorksheetFunction.XLookup(Range(“A21”), Range(“A13:I13”), Range(“A14:I14”))

End Sub

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:

1 2 3 4 5 6 7 8 9 |
Sub UsingVariables() Dim player As String Dim rngPlayers As Range Dim rngPoints As Range player = Range("A22") Set rngPlayers = Range("A13:I13") Set rngPoints = Range("A14:I14") Range("B22") = Application.WorksheetFunction.XLookup(player, rngPlayers, rngPoints) End Sub |

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.