VLOOKUP is one of the most used functions in Excel, and you can also use it easily in VBA as well.
VBA does not have a separate VLOOKUP function, so to use VLOOKUP in a VBA code, you need to use the same VLOOKUP function that you use in he worksheet.
In this article, I will show you how to use VLOOKUP in VBA and some examples of using it in different scenarios.
Let’s get into it!
VBA VLOOKUP Syntax
Below is the wheel lookup syntax in VBA.
Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)
As you might have already noticed, the syntax of the VLOOKUP function looks exactly the same as that you use in the worksheet.
That is because when using VLOOKUP in VBA, we are referring to the same VLOOKUP function that you already know.
Using VLOOKUP in VBA (Examples)
Now, let’s see a couple of examples of using VLOOKUP in VBA.
Fetch a Value Using VLOOKUP in VBA
Below, I have a data set where I have student names in column A and their scores in column B, and I want to fetch their scores of Brandon.
Here is the VBA code that will do that and show me Brandon’s score in a message box.
Sub GetScore()
score = Application.VLookup("Brandon", Range("A2:B16"), 2, False)
If IsError(score) Then
MsgBox "Brandon's score not found."
Else
MsgBox "Brandon's score: " & score
End If
End SubThe above code uses the VLOOKUP function to go through the leftmost column in the specified range (A2:B16), look for the name ‘Brandon’, and when it finds the name, show us the score in a message box.
In case the code is not able to find the name in column A, it will show the message “Brandon’s score not found.”
If you want the VB code to prompt the user to enter the name to look for, and then get its score, you can use the below code:
Sub GetScoreUsingVLOOKUP()
Dim studentName As String
Dim score As Variant
' Ask the user to enter the student's name
studentName = InputBox("Enter the student's name:", "Student Name")
' Check if the user entered a name
If studentName <> "" Then
' Perform VLOOKUP with the entered name
score = Application.VLookup(studentName, Range("A2:B16"), 2, False)
' Check if the score is found and display a message
If IsError(score) Then
MsgBox "Score not found for " & studentName & "."
Else
MsgBox studentName & "'s score: " & score
End If
Else
MsgBox "No name entered."
End If
End SubIn the VBA codes shown above, I have hard-coded the range that has the data. You can also use a named range instead of the range reference.
VLOOKUP From Another Sheet Using VBA
You can also use VLOOKUP to go through the data in another sheet and then give us the result.
Below I have a data set of students names in column A and their scores in column B in a sheet named ‘Scores’.
And I have the following table with some student names in column A in a separate sheet named ‘Result’.
I want to use VBA VLOOKUP to fetch the results sheet by going through the data in the scores sheet.
Below is the code that will do this:
Sub VLOOKUPAnotherSheet()
For Each cell In Range("A2:A5")
cell.Offset(0, 1) = Application.VLookup(cell, Sheets("Scores").Range("A2:B16"), 2, False)
Next
End SubThe above code goes through each cell in the range A2:A5 in the active sheet (which is the Result sheet), and uses the name in the cell as the look up value.
It then goes to the Scores sheet and scans the names in A2:A16. When it finds the name, it fetches the score from column B and then puts that score next to the name in the Result sheet.
In case it doesn’t find the score for a given name, it would return the #N/A error.
Also read: VBA Check IF Cell is Empty (ISEMPTY Function)
VLOOKUP From Another Workbook Using VBA
You can also use the VLOOKUP function in VBA to refer to another workbook and fetch the value from there.
Below, I have a data set of students’ names in column A and their scores in column B. This data is in a workbook named Scores.xlsx and the sheet named ‘Data’.
And I have the below dataset in another workbook:
Now, I want to use VLOOKUP to go through the names in the Scores.xlsx workbook, fetch the scores for each name, and put it in the adjacent cell.
Here is the VBA code that will do this:
Sub VLOOKUPAnotherWorkbook()
Dim cell As Range
For Each cell In Range("A2:A5")
cell.Offset(0, 1) = Application.VLookup(cell, Workbooks("Scores.xlsx").Sheets("Data").Range("A2:B16"), 2, False)
Next
End SubThe above code goes through each cell in the range A2:A5 in the active sheet.
For each name in this range, it does a VLOOKUP by going to the Scores.xlsx workbook and looking for the names in the A1:A16 range in the Data worksheet.
When it finds the name, it returns the score for that name back in column B in the active sheet.
Note: For this code to work, it is necessary that the other worksheet from which you are getting the value using Vlooku is open. If that workbook is not open, you will get a Runtime error in the code.
VLOOKUP From Another Closed Workbook Using VBA
If you want to use VLOOKUP to get the values from a closed workbook, you need to first open the workbook using your code and then go through the data from it using a Vlookup function.
The result that you then find in this workbook would then be written back into the active sheet.
Let me explain with an example.
Below, I have a data set in a sheet named ‘Result,’ where I have some names in column A, and I want to get their scores from a closed workbook named Scores.xlsx. In this closed workbook, the scores are provided in a table in the sheet named ‘Data’.
Here is the code that will work in this case:
Sub VLOOKUPAnotherWorkbook()
Dim cell As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim result As Variant
Dim workbookIsOpen As Boolean
' Check if Scores.xlsx is already open
workbookIsOpen = False
For Each wb In Workbooks
If wb.Name = "Scores.xlsx" Then
workbookIsOpen = True
Exit For
End If
Next wb
' Open Scores.xlsx if it's not already open
If Not workbookIsOpen Then
Set wb = Workbooks.Open("C:\Users\sumit\Downloads\Scores.xlsx")
Else
Set wb = Workbooks("Scores.xlsx")
End If
Set ws = wb.Sheets("Data")
' Loop through cells and apply VLOOKUP
For Each cell In ThisWorkbook.Sheets("Result").Range("A2:A5")
result = Application.VLookup(cell.Value, ws.Range("A2:B16"), 2, False)
' Check if VLOOKUP returned an error
If IsError(result) Then
cell.Offset(0, 1).Value = "Not Found" ' Or any other default value
Else
cell.Offset(0, 1).Value = result
End If
Next cell
' Close the workbook if it was not open before
If Not workbookIsOpen Then
wb.Close SaveChanges:=False
End If
End SubThe above VBA macro code loops through a range of cells in the “Result” sheet of the current workbook and performs a VLOOKUP in the “Data” sheet of “Scores.xlsx”.
The results are written next to the original cells. If no match is found, “Not Found” is written.
It also handles the opening and closing of “Scores.xlsx” intelligently, ensuring it only closes the workbook if it wasn’t already open when the macro began.
Also read: VBA Copy Sheet to New/Existing Workbook
Error handling When Using VLOOKUP in VBA
Here are a few tips to handle errors when using Vlookup in VBA in Excel.
Using ISERROR
You can use the ISERROR function in VBA to check whether VLOOKUP has returned an error or not, and in case it has returned an error, then handle it.
Below is an example code on handling errors given by VLOOKUP in VBA:
Sub VLOOKUPError()
Dim result As Variant
result = Application.VLookup(lookupValue, tableArray, colIndex, False)
If IsError(result) Then
result = "Not Available" ' Set to your default value
End If
End Sub The above code uses a variable result to store the result of the VLOOKUP formula.
It then uses an IF statement to check the value of IsError(result). If IsError(result) is True, which means that VLOOKUP has returned an error, it will execute the lines of codes in the IF condition (which is to set the value of the result as ‘Not Available’)
Also read: Error Handling in VBA
Using On Error Resume Next
Another way you can handle errors given by VLOOKUP function is by using On Error Resume Next.
When we add this line to our code, it ignores any errors it encounters and moves on to executing the next line of code.
This can be used if you expect some errors to happen and do not want the code to stop because of them. However, It’s important to use this with caution as it can make debugging difficult by ignoring all errors.
In this article, I showed you how to use the VLOOKUP function in VBA in Excel using some examples.
If you have any feedback or suggestions for me, do let me know in the comments section.
Other Excel VBA articles you may also like:
Other Excel articles you may also like:
