How to Use Excel VBA VLookup Within Loop

Get FREE Advanced Excel Exercises with Solutions!

Have you ever spent hours manually matching data across multiple sheets or tables in Excel? The tedious and time-consuming task can leave you feeling frustrated and overwhelmed. And here comes the VLookup function to save your time and energy. Excel VBA VLOOKUP loop is a powerful tool that we may use to automate data analysis tasks in Excel. In this article, we’ll explore how to use Excel VBA VLookup Within Loop. To get an overview of what our task looks like, you may watch this short video:

So, without further intro, let’s dive into the core point!


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.

We’ll use VBA to use the VLookup function in this article. So, it’ll be handy if we get a brief overview of how VLookup works. You may 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 that it looks for is in the leftmost column of the given table. 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 number of the column 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. You’ll get the details of how we can use this concept in a loop by following this article.
(one or two examples)


How to Launch VBA Editor in Excel

As we’ll use the VBA environment throughout this work, we need to enable the VBA setup first if not done so. First of all, open the Developer tab. If you don’t have the developer tab, then you have to enable the Developer tab. Then select the Visual Basic command.

Opening VBA window

Hence, the Visual Basic window will open. Then, we’ll insert a Module from the Insert option where we’ll write the VBA Code.

Inserting Module

Whenever we’ll write the code, we’ll start by following these steps first.


How to Vlookup in a Range Using Excel VBA 

Before anything, to get a glimpse of how VLookup works, you may watch this video:

We use VLookup to find data based on other data. If we need to perform this operation multiple times, you simply use VLookup multiple times. But that won’t be an efficient technique, especially if you have a large dataset to work on. 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 every single time manually.

To get familiar with VLookup in Excel VBA, you may consider this example. We have a dataset of Marksheet of some students like this:

Image of Example Dataset

Now 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:
Output of ExampleDon’t worry if you can’t thoroughly follow this code. This is just an example of what the VLOOKUP looks like. We’ll cover detailed code explained line by line in the upcoming examples.


Excel VBA VLookup Within Loop: 3 Examples

To be specific about this article, we’ll use a dataset that contains sales data. In this dataset, we have data under the following headers
Salesperson ID, Salesperson Name, Product, Product ID, and Total Sales.
We’ll demonstrate different use cases of VLookup  in a loop based on this dataset. We’ll focus on covering every essential detail in such a way so that you may get the full grasp of using the VLookup within a loop in Excel VBA. So let’s start exploring together!


1. Using the VLookup to Fill a List on a Different Sheet

The first example will demonstrate to you how we can use VLookup in a loop to fill a list on a different sheet. To get an overview of what this task looks like you may check this video.

In this case, we have a dataset under the worksheet named Dataset(Source) that contains Salesperson ID and other information like this :

Exe1 Image of dataset

We have another worksheet named FillData where we have the Salesperson ID and the corresponding  Salesperson Name is blank there.

Exe1 Second Image of Dataset

Now we need to fill in the corresponding Salesperson Name in the FillData worksheet. For this reason, we’ll use the VLOOKUP with a loop in Excel VBA.

Steps:

  • We’ve enabled and started coding as mentioned in the How to Launch VBA Editor segment.
  • Then, write the following code in Module 1. To make further reference easier, I’ll rename Module 1 as GetSalesPerson as our focus for this module is to get the salesperson’s name from Dataset(Source) Worksheet to FillData Worksheet.

Exe1 Image of Code

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 an integer.

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 will iterate through each row in the destination sheet from row 5 to lastRow.
SalesID = ActiveCell
  • Here, we’ve created the SalesID variable which is assigned the value of the active cell, 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 down in the destination sheet.
Next i
  • This statement increases the value of i by 1.
End Sub
  • This statement ends the GetSalesPerson subroutine.
  • Now, press F5 or click the Run button to run the code.

Run the Macro

After running the macro, it’ll fill in the corresponding Salesperson Name in the FillData worksheet. Hence, the final output will look like this and we’ll get the desired output which is all the names of the salesperson just in one click.

Read More: Excel VBA Vlookup with Multiple Criteria


2. Working with VLookup on Same Worksheet

In this example, we’ll work on how to get data from the same worksheet using VLookup in Excel VBA. To get an overview of how we’ve done this, you may check this video.

Our dataset looks like this:

Exe2 Dataset Image

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 Salesperson ID and Department in columns I and J respectively. This Department data is the master dataset that contains all the employees of that company. 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.
  • Then, write 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.

Exe2 Image of Code to get Dept

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
  • Now 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 that value 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.
  • Now 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


3. Vlookup Multiple Values from a List

Though VLookup is very useful in Excel VBA, it has its shortcomings. The major shortcoming is that it can’t be used to get multiple values at a time. For example, if we want to get the product names sold by a specific salesperson, it won’t be a straightforward task to get the thing done using the VLookup function. In this scenario, we can accomplish the task by using our own defined function. To get an overview of how we’re going to perform this task, you may check this video.

Our dataset looks like this:

 Exe3 Dataset for Finding Multiple Values

Steps:

  • We’ve enabled and started coding as mentioned in the How to Launch VBA Editor segment.
  • Then, write 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.

Exe3 Image of Code

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 so that it’ll be 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 value of the current cell being 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 (which is 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 name of the salesperson 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 column number of the data 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 available 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 that contains the name of the salesperson. The second element is the result of the MultipleValueFinding function which is 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:

In this way, we get the names of the products sold by the specific salesperson.

Read More: Excel VBA to Vlookup Values for Multiple Matches


Frequently Asked Questions

1. How do you automate VLookup in VBA?

To automate VLookup in VBA, you need to use it in a loop. This way, you may run VLookup in VBA multiple times automatically. This article contains this type of working example.

2. How do you run a loop in Excel VBA?

You may use the For loop to run a loop in Excel VBA. To run a For loop, we have to define the number of iterations by setting the condition.

3. How do you repeat a VLOOKUP in Excel?

To repeat a VLOOKUP in Excel, you may simply use the Fill Handle. Alternatively, you may copy-paste the formula and make necessary adjustments to repeat the VLOOKUP. In the case of VBA, you may use the VLOOKUP inside a loop. We’ve shown examples of this in this article.

4. How do you continue a loop in Excel VBA?

VBA doesn’t have an option such as Continue Statement. You may use GoTo statement or Step alternatively.


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. So get your data organized in that way if already do not.
  • Use the For loop carefully, especially take care 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.


Conclusion

I’ve already discussed the pros and cons of using the Excel VBA VLookup Within Loop. Also, I’ve demonstrated several detailed examples. In conclusion, a VLookup loop in Excel VBA can be a powerful tool for automating tasks that involve looking up values in large datasets. By using a For loop, setting up the VLookup formula correctly, and including error handling code, you can efficiently loop through a range of cells and perform the VLookup operation on each row. I hope this article helps you in the best possible way. If you have any queries, please let us know. Have a good day!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hadi Ul Bashar
Hadi Ul Bashar

Hadi Ul Bashar holds a BSc degree in Biomedical Engineering from Bangladesh University of Engineering and Technology.  In January 2023, Bashar joined SOFTEKO as an Excel and VBA content developer. Since then, he has authored over 50 articles. He was engaged in two specialized training programs focused on VBA and Chart & Dashboard designing in Excel. Currently he is creating video content on Excel and VBA and has created over 70 videos. Reading books and traveling are his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo