Learn how to use VBA and XLOOKUP Function together. In this example, the VBA macro will prompt the user to enter a search value, and then use the
XLOOKUP function to search for that value in the
searchRange (column A) and return the corresponding result from the
resultRange (column B). The result will be displayed in a message box.
Sub UseXLOOKUP() Dim searchValue As Variant Dim searchRange As Range Dim resultRange As Range 'Set search value searchValue = InputBox("Enter the search value") 'Set the search range Set searchRange = Range("A1:A10") 'Set the result range Set resultRange = Range("B1:B10") 'Use XLOOKUP to return the result MsgBox Application.Run("XLOOKUP", searchValue, searchRange, resultRange) End Sub
Dim searchValue As String: This line declares a variable named
searchValuewith data type
String. This variable will be used to store the value entered by the user in the input box, which represents the name they want to search for.
Dim searchRange As Rangeand
Dim resultRange As Range: These lines declare two variables named
resultRange, respectively, with data type
Range. These variables will be used to store the ranges of cells in the spreadsheet that contain the names and corresponding ages, respectively.
searchValue = InputBox("Enter the name to search:"): This line displays an input box where the user can enter the name they want to search for. The value entered by the user is stored in the
Set searchRange = Range("A2:A10")and
Set resultRange = Range("B2:B10"): These lines set the values of the
resultRangevariables to the ranges of cells in the spreadsheet that contain the names and corresponding ages, respectively. In this example, the names are stored in the range
A2:A10, and the ages are stored in the range
Dim age As Variant: This line declares a variable named
agewith data type
Variant. This variable will be used to store the result of the
age = Application.Run("XLOOKUP", searchValue, searchRange, resultRange): This line uses the
Application.Runmethod to run the
XLOOKUPfunction and store the result in the
resultRangevariables are passed as arguments to the function.
MsgBox searchValue & "'s age is: " & age: This line displays a message box with the result of the search. The message box shows the name that was searched for, along with the corresponding age. The
&operator is used to concatenate the strings in the message box.