Table of Contents
The VBA Vlookup function is an easy-to-use tool for looking up values from another sheet. It takes four arguments: a lookup value, a table array, the column number containing the value to be returned, and an optional logical value to specify whether exact or approximate matches should be used. The lookup value is the value you want to look up in the other sheet, the table array is the range of data from the other sheet, the column number is the column containing the value to be returned, and the logical value is an optional argument to specify whether exact or approximate matches should be used. Once these parameters are defined, the function searches the table array for the lookup value and returns the value from the specified column.
You can use the following basic syntax to perform a VLOOKUP from another sheet using VBA:
Sub Vlookup()
Range("B2").Value = WorksheetFunction.Vlookup(Range("A2"), Sheets("Sheet2").Range("A2:C11"),3,False)
End Sub
This particular example looks up the value in cell A2 of the current sheet in the range A2:C11 of the sheet called Sheet2 and finds the corresponding value in the third column of the range and then assigns the result to cell B2 in the current sheet.
Note: The last argument of False specifies that we want an exact match.
The following example shows how to use this syntax in practice.
Example: How to Use VLOOKUP From Another Sheet in VBA
Suppose we have the following dataset in a sheet called Sheet2 in an Excel workbook that contains information about various basketball players:
Now suppose we switch to the sheet called Sheet1 and would like to look up the team name “Kings” in the dataset from Sheet2 and return the corresponding value in the assists column in Sheet1.
We can create the following macro to do so:
Sub Vlookup()
Range("B2").Value = WorksheetFunction.Vlookup(Range("A2"), Sheets("Sheet2").Range("A2:C11"),3,False)
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 A2 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: You can find the complete documentation for the VBA VLookup method .