Use VBA and XLOOKUP together!

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

1. Dim searchValue As String: This line declares a variable named searchValue with 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.
2. Dim searchRange As Range and Dim resultRange As Range: These lines declare two variables named searchRange and 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.
3. 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 searchValue variable.
4. Set searchRange = Range("A2:A10") and Set resultRange = Range("B2:B10"): These lines set the values of the searchRange and resultRange variables 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 B2:B10.
5. Dim age As Variant: This line declares a variable named age with data type Variant. This variable will be used to store the result of the XLOOKUP function.
6. age = Application.Run("XLOOKUP", searchValue, searchRange, resultRange): This line uses the Application.Run method to run the XLOOKUP function and store the result in the age variable. The searchValue, searchRange, and resultRange variables are passed as arguments to the function.
7. 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.