How to Use XLOOKUP in VBA (With Examples)

You can use the following basic syntax to perform a XLOOKUP using VBA:

Sub Xlookup()
    Range("F2").Value = WorksheetFunction.Xlookup(Range("E2"), Range("A2:A11"), Range("C2:C11"))
End Sub

This particular example looks up the value in cell E2 in the range A2:A11 and finds the corresponding value in the range C2:C11 and then assigns the result to cell F2.

The following example shows how to use this syntax in practice.

Example: How to Use XLOOKUP in VBA

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to look up the team name “Kings” in the dataset and return the corresponding value in the assists column.

We can create the following macro to do so:

Sub Xlookup()
    Range("F2").Value = WorksheetFunction.Xlookup(Range("E2"), Range("A2:A11"), Range("C2:C11"))
End Sub

When we run this macro, we receive the following output:

The macro correctly returns a value of 3 assists for the Kings.

If we change the name of the team in cell E2 and then run the macro again, it will correctly find the assists value for the new team name.

For example, suppose we change the team name to “Warriors” and run the macro again:

The macro correctly returns a value of 4 assists for the Warriors.

Note that you can also add an optional fourth argument to the Xlookup function to specify the value that should be displayed if no match is found.

For example, you could use the following macro to perform an XLOOKUP function and return “None” if not match is found:

Sub Xlookup()
    Range("F2").Value = WorksheetFunction.Xlookup(Range("E2"), Range("A2:A11"), Range("C2:C11"), "None")
End Sub

Feel free to replace “None” with any value that you’d like to display.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Use INDEX MATCH
VBA: How to Use VLOOKUP
VBA: How to Use VLOOKUP From Another Sheet