Table of Contents
XLOOKUP is a built-in Excel function that can be used in Visual Basic for Applications (VBA) code to perform a lookup and return a value from a range of cells. This function is similar to the VLOOKUP function, but it has more features and is easier to use. To use XLOOKUP in VBA, you must first define the range from which you want to lookup the value, the value to look for, and the column whose value you want to return. You can then use the XLOOKUP function in your VBA code to execute the lookup and return the value.
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.
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.