Overview of VBA VLookup
So, what is VLookup?
VLookup is a function in Excel that allows you to search for a value in a dataset and return a corresponding value from the same row. We use it mostly to match data across different datasets in the same worksheet or even in different worksheets.
In this article, we’ll use VBA to use the VLookup function, so it’ll be handy to get a brief overview of how it works. You can get the details of VLookup from here if you need them.
The Syntax of the VLookup function is:
VLookup(lookup_value,table_array,col_index_num,[range_lookup])
Argument | Required/Optional | Value |
---|---|---|
lookup_value | Required | The value it looks for is in the leftmost column of the given table. It can be a single value or an array of values. |
table_array | Required | The table in which it looks for the lookup_value in the leftmost column. |
col_index_num | Required | The column number in the table from which a value is to be returned. |
[range_lookup] | Optional | Tells whether an exact or partial match of the lookup_value is required. 0 for an exact match, 1 for a partial match. The default is 1 (partial match). |
VLookup works by searching for a value in the leftmost column and then returning a value from the same row in a specified column. For example, if you have a dataset where there are two columns named “Salesperson ID” and “Salesperson Name”, and you want to find the Name of a salesperson, you would use VLookup to search for the Salesperson ID in the “Salesperson ID” column, then return the corresponding name from the “Salesperson Name” column. Following this article, you’ll get the details of how we can use this concept in a loop.
(one or two examples)
How to Launch VBA Editor in Excel
STEPS:
- Open the Developer tab. If you don’t have the Developer tab, you must enable it.
- Select the Visual Basic command.
- The Visual Basic window will open.
- Insert a Module from the Insert option where we’ll write the VBA Code.
Whenever we write the code, we start by following these steps first.
How to Vlookup in a Range Using Excel VBA
To get a glimpse of how VLookup works, watch this video:
We use VLookup to find data based on other data. If we need to perform this operation multiple times, you use VLookup multiple times. But that won’t be an efficient technique, especially if you have a large dataset. In this case, the loop comes into action. You can perform the task of using VLookup for as much time as you need with a single command. All you have to do is set the VLookup condition in a loop and define the loop iterations. Then, you’ll get the data as the loop iterates. You don’t need to run VLookup manually every single time.
To get familiar with VLookup in Excel VBA, consider this example. We have a dataset of Marksheet of some students like this:
We want to get the marks of a specific student from the dataset. We can solve this problem using VLookup in Excel VBA. Here, we’ll take the Student ID as input from the user and get the corresponding Marks as output. The code will look like this:
Sub LookupMarks()
Dim student As Variant
Dim lookupRange As Range
Dim lookupValue As Variant
Dim result As Variant
' Get the student ID input by the user
studentID = InputBox("Enter Student ID:")
' Set the lookup range to the Student ID and Marks columns (A and C)
Set lookupRange = Range("B5:D9")
' Get the marks for the student from the same row using VLOOKUP
result = Application.VLookup(studentID, lookupRange, 3, False)
' show output
MsgBox "Marks of Student " & studentID & " is " & ": " & result
End Sub
If we set S2 as input, the output will look like this:
This is just an example of what the VLOOKUP looks like. We’ll cover detailed code explained line by line in the upcoming examples.
Method 1 – Using the VLookup to Fill a List on a Different Sheet
To get an overview of what this task looks like you may check this video.
We have a dataset under the worksheet named Dataset(Source) that contains Salesperson ID and other information like this :
We have another worksheet named FillData, on which we have the Salesperson ID, but the corresponding Salesperson Name is blank.
We need to fill in the corresponding Salesperson Name in the FillData worksheet.
Steps:
- As mentioned in the How to Launch VBA Editor segment, we’ve enabled and started coding.
- Enter the following code in Module 1. To make further reference easier, I’ll rename Module 1 GetSalesPerson, as our focus for this module is to get the salesperson’s name from the Dataset(Source) Worksheet to the FillData Worksheet.
Sub GetSalesPerson()
Dim SalesID As String
Dim PersonName As String
Dim i As Integer
'starting cell selection in the destination sheet _
based on which data will be extracted from the source sheet
Worksheets("FillData").Range("B5").Select
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
'loop for all rows in the destination sheet
For i = 5 To lastRow
SalesID = ActiveCell
On Error Resume Next ' Turn on error handling
PersonName = Application.WorksheetFunction.VLookup(SalesID, _
Worksheets("Dataset(Source)").Range("B4:F13"), 2, False)
On Error GoTo 0 ' Turn off error handling
' Check if Vlookup found a match for the salesman ID
If IsError(PersonName) Then
ActiveCell.Offset(0, 1).Value = "Salesman ID not found"
Else
ActiveCell.Offset(0, 1).Value = PersonName
End If
'loop to the next cell
ActiveCell.Offset(1, 0).Select
Next i
End Sub
Code Breakdown
Here, I’ve shown the code breakdown to ease your understanding.
- I’ve created a subroutine named GetSalesPerson.
- Then, I declared SalesID and PersonName as string variables and i as integers.
Worksheets("FillData").Range("B5").Select
- This will select the starting cell in the destination worksheet where the data will be extracted from the source worksheet.
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
- This will get the last row of data in column “B” of the destination sheet.
For i = 5 To lastRow
- This will initiate a For loop, which iterates through each row in the destination sheet from row 5 to the last row.
SalesID = ActiveCell
- Here, we’ve created the SalesID variable, which is assigned the active cell’s value, initialized with B5.
On Error Resume Next
- This statement will enable error handling.
PersonName = Application.WorksheetFunction.VLookup(SalesID,Worksheets("Dataset(Source)").Range("B4:F13"), 2, False)
- This statement assigns the value returned by the VLookup function to PersonName variable. Note that, VLookup searches for a match to the SalesID in the dataset range B4:F13 in the source sheet and returns the value in the second column of the matched row. False argument specifies that we are looking for an exact match.
On Error GoTo 0
- We’ve used this statement to disable error handling.
If IsError(PersonName) Then
ActiveCell.Offset(0, 1).Value = "Salesman ID not found"
Else
ActiveCell.Offset(0, 1).Value = PersonName
End If
- This If statement checks if the PersonName variable contains an error value using the IsError function. If this finds an error, then it writes Salesman ID not found in the cell to the right of the active cell in the destination sheet. Otherwise, it writes the valid value in the cell to the right of the active cell.
ActiveCell.Offset(1, 0).Select
- This statement moves the active cell to the next row in the destination sheet.
Next i
- This statement increases the value of i by 1.
End Sub
- This statement ends the GetSalesPerson subroutine.
- Press F5 or click the Run button to run the code.
After running the macro, it will fill in the corresponding Salesperson Name in the FillData worksheet.
The final output will look like this, and we’ll get the desired output: all the salesperson’s names in one click.
Read More: Excel VBA Vlookup with Multiple Criteria
Method 2 – Working with VLookup on the Same Worksheet
Watch this video to get an overview of how we’ve done this.
Our dataset looks like this:
Here, We have an active worksheet named DataFillUsingVLOOKUP. We’ve sales information like Salesperson Name, Salesperson ID, and other relevant data ranging from column B to G where the Department section is missing. On the other hand, we’ve included the Salesperson ID and Department in columns I and J. This Department data is the master dataset containing all the company’s employees. Now, we need to get the Department data of the salesperson in column G using VLookup. In this case, we’ll get a Salesperson ID from column B, find that data in column I, and return the matched department in column G.
Steps:
- We’ve enabled and started coding, as mentioned in the How to Launch VBA Editor segment.
- Enter the following code in Module 2. To make further reference easier, I’ll rename Module 2 as GetSalesDepartment as our focus for this module is to get the salesperson’s department from the same worksheet named DataFillUsingVLOOKUP.
Sub GetSalesDepartment()
Dim lastRow As Long
Dim i As Long
Dim dept As Variant
' Find the last row of data in column C
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
' Loop through each row in column C
For i = 5 To lastRow
' Use Vlookup to find the department for the salesman ID in column C
On Error Resume Next ' Turn on error handling
dept = Application.VLookup(Cells(i, "C").Value, Range("I:J"), 2, False)
On Error GoTo 0 ' Turn off error handling
' Check if Vlookup found a match for the salesman ID
If IsError(dept) Then
Cells(i, "G").Value = "Salesman ID not found"
Else
Cells(i, "G").Value = dept
End If
Next i
End Sub
Code Breakdown
Now, I’ll show you the code breakdown to ease your understanding.
Sub GetSalesDepartment()
- First of all, I’ve created a subroutine named GetSalesDepartment.
Dim lastRow As Long
Dim i As Long
Dim dept As Variant
I’ve declared three variables as follows: lastRow and i as long, dept as a variant.
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
- The lastRow variable is set to the last row of data in column C. Hence, we’ll get the last row of SalesPersonID.
For i = 5 To lastRow
- Then I initiated a For loop to iterate through each row in the SalesPersonID column ranging from row 5 (value starts here) to lastRow.
On Error Resume Next
- I’ve used this statement to enable error handling.
dept = Application.VLookup(Cells(i, "C").Value, Range("I:J"), 2, False)
- Now, the VLookup function searches for a match to the value in column C of the current row in the range I:J (Department data exists in this range). And returns the value in the second column of the matched row. The fourth argument of the VLookup function is False, which means we’re looking for an exact match. The returned value is assigned to the dept variable.
On Error GoTo 0
- This disables the error handling.
If IsError(dept)
Then Cells(i, "G").Value = "Salesman ID not found"
Else
Cells(i, "G").Value = dept
End If
- The If statement checks if the dept variable contains an error value using the IsError function. If so, it writes Salesman ID not found in column G of the current row. If the dept variable contains a valid value, it writes it in column G of the current row.
Next i
- This statement increments the value of i by one and runs the loop for the next iteration.
End Sub
- This statement ends the GetSalesDepartment subroutine.
- Press F5 or click the Run button to run the macro.
As a result, we’ll find the department for each salesperson ID in column C of the active worksheet and write the department name in column G of the same row using VLookup in Excel VBA. Our output looks like this:
This is useful for quickly determining the department of each salesperson in a dataset.
Read More: Excel VBA: Working with If, IsError, and VLookup Together
Method 3 – Vlookup Multiple Values from a List
To get an overview of how we will perform this task, watch this video.
Our dataset looks like this:
Steps:
- We’ve enabled and started coding, as mentioned in the How to Launch VBA Editor segment.
- Enter the following code in Module 3. To make further reference easier, I’ll rename this Module 3 as MultipleValues, as our focus for this module is to get the name of the products sold by a specific salesperson from MultipleValueFinding Worksheet.
Sub FindProductsSoldBySalesperson()
Dim Lookupvalue As String
Dim LookupRange As Range
Dim ColumnNumber As Integer
Dim ResultRange As Range
' Get the name of the salesperson as input
Lookupvalue = InputBox("Enter the name of the salesperson:")
' Get the user to select the lookup range from user by selecting in the Excel Worksheet
Set LookupRange = Application.InputBox _
("Select the lookup range:(range containing Salesperson and Product)", Type:=8)
' Get the user to enter the column number of the data
ColumnNumber = InputBox _
("Enter the column number of the data:(Column no of Product according to your selection)")
' Find the next empty cell in column
Dim nextRow As Integer
nextRow = Range("H" & Rows.Count).End(xlUp).Row + 1
' Set the range to display the result
Set ResultRange = Range("H" & nextRow & ":I" & nextRow)
' Call the SingleCellExtract function to extract the data
ResultRange.Value = _
Array(Lookupvalue, MultipleValueFinding(Lookupvalue, LookupRange, ColumnNumber))
End Sub
Function MultipleValueFinding(Lookupvalue As String, _
LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
Dim Output As String
For i = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(i, 1) = Lookupvalue Then
Output = Output & " " & LookupRange.Cells(i, ColumnNumber) & ","
End If
Next i
MultipleValueFinding = Left(Output, Len(Output) - 1)
End Function
Code Breakdown:
I’ve simplified the code breakdown to make it easier for you to understand the code.
Let’s begin with the MultipleValueFinding function.
Function MultipleValueFinding(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
- We’ve defined a function named MultipleValueFinding, which takes three arguments: Lookupvalue (a string), LookupRange (a range), and ColumnNumber (an integer).
Dim i As Long
Dim Output As String
- Then we’ve declared two variables: i, which is a long integer we’ll use as a counter in a loop, and Output, which is a string that we will use to store the results of the function.
For i = 1 To LookupRange.Columns(1).Cells.Count
- I’ve set a For loop to iterate through each cell in the first column of LookupRange, from row 1 to the last row with data.
If LookupRange.Cells(i, 1) = Lookupvalue Then
Output = Output & " " & LookupRange.Cells(i, ColumnNumber) & ","
End If:
- This If statement will check whether the current cell’s value examined in the loop matches the Lookupvalue passed into the function. If the current cell’s value matches the Lookupvalue, then the value in the cell in the ColumnNumber column of the same row is concatenated to the Output string, separated by a space and a comma.
- Next i: The For loop iterates through each cell in the first column of LookupRange until it reaches the last row with data.
MultipleValueFinding = Left(Output, Len(Output) - 1)
- The MultipleValueFinding function returns a string consisting of all the values found in ColumnNumber of LookupRange for the rows where the first column matches Lookupvalue, separated by a space and a comma. The Left function returns all characters of the Output string except for the last character (a comma).
Now, we’ll call this MultipleValueFinding function to get the names of the products sold by a specific salesperson. For this:
Code Breakdown
Sub FindProductsSoldBySalesperson()
- Declares a subroutine named FindProductsSoldBySalesperson.
Dim Lookupvalue As String
Dim LookupRange As Range
Dim ColumnNumber As Integer
Dim ResultRange As Range:
Declares 4 variables: Lookupvalue as a String variable to store the name of the salesperson. LookupRange is a Range variable to store the range of cells to lookup. ColumnNumber is an Integer variable to store the column number of the data. ResultRange as a Range variable to store the range where we’ll display the result.
Lookupvalue = InputBox("Enter the name of the salesperson:")
- This statement prompts the user to enter the salesperson’s name and stores the value in the Lookupvalue variable using an input box.
Set LookupRange = Application.InputBox ("Select the lookup range:(range containing Salesperson and Product)", Type:=8)
- This statement prompts the user to select the range of cells to look up and stores the range object in the LookupRange variable using the Application.InputBox method. The Type:=8 parameter indicates that the user should select a Range.
ColumnNumber = InputBox("Enter the column number of the data:(Column no of Product according to your selection)")
- This statement prompts the user to enter the data’s column number and stores the value in the ColumnNumber variable using an input box.
Dim nextRow As Integer
nextRow = Range("H" & Rows.Count).End(xlUp).Row + 1
This statement declares a new integer variable nextRow to hold the next row number to add data to in column H. Note that column H contains the Salesperson Name. The Range(“H” & Rows.Count).End(xlUp).Row + 1 finds the last used row in column H and adds 1 to get the next empty row. We’ve done this to enter the new output in the next empty cell.
Set ResultRange = Range("H" & nextRow & ":I" & nextRow)
- This statement sets the range object ResultRange to the range of cells in columns H and I corresponding to the next empty row found in the previous step. In column H, we’ll show the names of the products sold by a specific salesperson.
ResultRange.Value = _ Array(Lookupvalue, MultipleValueFinding(Lookupvalue, LookupRange, ColumnNumber))
- This statement sets the value of the ResultRange to an array of two elements. The first element is the Lookupvalue variable, which contains the salesperson’s name. The second element is the result of the MultipleValueFinding function, which contains the names of the products sold by this specific salesperson, which we’ve described earlier.
End Sub
- This statement ends the FindProductsSoldBySalesperson subroutine.
The output looks like this:
This way, we get the names of the products sold by the specific salesperson.
Read More: Excel VBA to Vlookup Values for Multiple Matches
Things to Remember
When using the VLookup in a loop in Excel VBA, there are several things to keep in mind to ensure that your code runs smoothly and efficiently:
- VLookup starts searching from the value in the leftmost column. If you haven’t already, organize your data that way.
- Use the For loop carefully, especially when setting the iterating conditions.
- Update the loop counter according to your specific needs.
- Use Error Handling. This is useful in case you have options ready if an error happens.
Download Practice Workbook
You may download the Workbook from here and practice yourself.
Related Articles
- Use Excel VBA VLOOKUP to Find Values in Another Worksheet
- How to Use Excel VBA VLookup with Named Range
- Excel VBA to Vlookup in Another Workbook Without Opening