Excel VBA: Working with If, IsError, and VLookup Together

One of the most common tasks in Excel is performing lookups to retrieve data from a table or database. However, lookup operations can sometimes result in errors, which can cause problems in your VBA code. In this article, we will explore how to use the If, IsError and VLookup functions together in Excel VBA to perform error handling and ensure the smooth operation of your lookup operations. We’ll cover the basics of each function, provide examples of how they can be used together, and offer tips and tricks for working with these functions in your VBA code. In the last portion of the article, we will discuss some FAQs with detailed explanations.

We will provide a sample method to demonstrate how we use If, IsError and VLookup functions in Excel together to solve various types of problems.

Below we combined the If and IsError with the VLookup function altogether to get the salary distribution of employees based on their role in the company.

Basic Syntax and Use of VBA If, IsError, and VLookup Functions

The basic syntax of the If function is given below with the help of a VBA code.

This If function operates as a condition checker and allows the user to run code based on whether they satisfy a condition or not.

If (condition) Then
' code to execute if condition is true
Else
' code to execute if condition is false
End If

On the other hand, the basic syntax of the IsError function is given below,

The IsError function works as a gatekeeper for all the errors that happen inside a code. It returns logical TRUE or FALSE based on whether there is an error happening inside the parenthesis or not.

Dim result As Variant
result = 10 / 0
If IsError(result) Then
MsgBox "Error: " & result
Else
MsgBox "Result: " & result
End If

Finally, we are going to present the basic syntax of the VLookup function. The main purpose of the VLookup function is to find specific values inside a range of cells and then return values from a specific column with exact or nominal matches.

Dim result As Variant
Dim lookup_value As Variant
Dim table_array As Range
Dim column_index As Integer
lookup_value = "John"
Set table_array = Range("A1:B10")
column_index = 2
result = Application.VLookup(lookup_value, table_array, column_index, False)
MsgBox "Result: " & result

In the above code, the VLookup function searches the lookup_value in the given table array and the return values stored in the column index in the table array. VLookup is extensively used in comparing two tables and finding values from one table to another.

Note:

The VLookup function is part of the Application object in VBA, so it needs to be called with the Application prefix. Also, the range_lookup argument is optional, and False is used to perform an exact match in the example above.

How to Launch VBA Editor in Excel

You can easily launch the VBA editor by pressing Alt+F11 or using the Visual Basic command from the Developer tab.

First, go to the Developer tab and then click on the Visual Basic.

Then go to Insert > Module.
You will see an editor window pop up. You can put your code in there.

Note:

In any case, if you can’t find the Developer tab in the ribbon, then you have to enable the Developer tab from the options.

Is It Possible to Use IsError with VLookup in One Statement in Excel VBA?

We can certainly use the IsError function alongside the VLookup function. The IsError function acts as a gatekeeper for all kinds of errors. So if there is any Error happens, the IsError will detect the error and return logical TRUE. Based on this value users can take action and modify code.

For instance the below code,

Dim lookupValue As Variant
Dim result As Variant
lookupValue = "John"
result = Application.VLookup(lookupValue, Range("A1:B10"), 2, False)
If IsError(result) Then
MsgBox "Error: " & result
Else
MsgBox "Result: " & result
End If

This code is going to be placed on the below dataset.

The VLookup function will search for the lookup value in the range of cell A1:B10. If there is any issue in that range like value not found or range out of limit, then there will be an error and the VLookup function will return TRUE, and hence the message box showing the “Error” will pop up. Otherwise, the result will simply be shown.

Excel VBA If, IsError, and VLookup Functions: 3 Cases

We will present three separate examples of how the combination of IF, IsError, and VLookup functions in Excel VBA can solve various problems with ease. To avoid any compatibility issues, try to use the Excel 365 edition.

1. Extracting Student Marks Who Failed from a List

Here we are going to determine the students who failed in test based on their marks in the exam and present them in the range of cell D5:D11 using the combination of If, IsError, and VLookup functions. The students whose marks are over 60 are simply going to be presented with Passed. The other people are going to be presented with their scores and names side by side.

The output will be something like the below image,

Steps:

In the beginning, open the VBA editor window by pressing the shortcut key Alt+F11.
And then enter the following code in the code window.

Sub Student_Evaluation()
Set rng1 = Application.InputBox("Select student name", "Range selection", Type:=8)
Set rng2 = Application.InputBox("Select Range containing name and marks", _
"Range selection", Type:=8)
Set rng3 = Application.InputBox("Select output range", "Range selection", Type:=8)
For i = 1 To rng1.Rows.Count
studentName = rng1.Cells(i, 1).Value
studentMark = rng2.Cells(i, 2).Value
If IsError(Application.VLookup(studentName, rng2, 2, False)) Then
rng3.Cells(i, 1).Value = ""
ElseIf studentMark < 60 Then
rng3.Cells(i, 1).Value = studentName & " - " & studentMark
Else
rng3.Cells(i, 1).Value = "Passed"
End If
Next i
End Sub

VBA Code Breakdown

Sub Student_Evaluation()

This is the start of the Subroutine, named Student_Evaluation. It is a macro that takes input from the user and performs some actions based on that input.

Sub Student_Evaluation()

This line of code prompts the user to select a range containing student names. The InputBox function displays a dialog box that allows the user to select a range. The selected range is then assigned to the variable rng1.

Set rng2 = Application.InputBox("Select Range containing name and marks", "Range selection", Type:=8)

This line of code prompts the user to select a range containing student names and their marks. The InputBox function displays a dialog box that allows the user to select a range. The selected range is then assigned to the variable rng2.

Set rng2 = Application.InputBox("Select Range containing name and marks", "Range selection", Type:=8)

This line of code prompts the user to select an output range where the results will be displayed. The InputBox function displays a dialog box that allows the user to select a range. The selected range is then assigned to the variable rng3.

For i = 1 To rng1.Rows.Count

This is a loop that iterates over each row in the rng1 range, which contains the student names.

studentName = rng1.Cells(i, 1).Value

studentMark = rng2.Cells(i, 2).Value

These lines of code assign the value of the current student name and mark to the variables studentName and studentMark, respectively.

If IsError(Application.VLookup(studentName, rng2, 2, False)) Then

This is a conditional statement that checks if the student name exists in the rng2 range (which contains the student names and their marks). If the student’s name is not found, then the output range cell for that student is set to blank.

ElseIf studentMark < 60 Then
rng3.Cells(i, 1).Value = studentName & " - " & studentMark

If the student name is found in the rng2 range and the student mark is less than 60, then the output range cell for that student is set to the student name and mark concatenated with a hyphen.

Else
rng3.Cells(i, 1).Value = "Passed"

If the student’s name is found in the rng2 range and the student’s mark is greater than or equal to 60, then the output range cell for that student is set to “Passed”.

Next i

This is the end of the loop. The code inside the loop will be executed for each row in the rng1 range.

End Sub

That’s the end of the subroutine. Overall, this code allows the user to select ranges of student names and marks, and an output range where the results will be displayed. It then loops over each row in the student name range, checks if the student name exists in the marks range, and displays the student name and mark or “Passed” in the output range depending on whether the mark is below or above 60.

After entering the code, press the F5 or the Run command button as shown in the image below.

After executing the code, you will see the first input range asking for the student’s name appears.
Here you need to enter the student name range. We selected B5:B11.
Press Enter after this.
Then another range selection where you need to enter the range of the cell that contains the name and marks of the students.
From this selected range we are going to find the match from the student’s name. We select B5:C11.
Press Enter after this.
Right after this, you are gonna be asked about the output range where you are going to put your results.
We selected D5:D11.
Press Enter after this.
Now you can see that the students who passed are now shown and the failed one name with their marks shown in the range of cell D5:D11.

The whole process can be summarised and shown in the video below.

I hope this case gave you an idea to use the If, IsError and VLookup functions together in Excel VBA.

2. Determine Employee Salary

This is the main dataset upon which we have to work on. Here we got the Employee’s Name and their job title or their job role. Now we have to determine their Salary based on their role given in the range of cell C11:C14.

The outcome will look something like this.

Steps:

In the beginning, open the VBA editor window by pressing the shortcut key Alt+F11.
And then enter the following code in the code window.

Sub Salary_Find()
Set rng1 = Application.InputBox("Select Manager Name and Job Title", "Range selection", Type:=8)
Set rng2 = Application.InputBox("Select Range containing Salary Structure", "Range selection", Type:=8)
Set rng3 = Application.InputBox("Select output range", "Range selection", Type:=8)
For i = 1 To rng1.Rows.Count
ManName = rng1.Cells(i, 2).Value
salary = rng2.Cells(i, 2).Value
If IsError(Application.VLookup(ManName, rng2, 2, False)) Then
rng3.Cells(i, 1).Value = ""
Else
rng3.Cells(i, 1).Value = Application.VLookup(ManName, rng2, 2, False)
End If
Next i
End Sub

Code Breakdown

Sub Salary_Find()

This is the start of the Subroutine named Salary_Find. It is a macro that takes input from the user and performs some actions based on that input.

Set rng1 = Application.InputBox("Select Manager Name and Job Title", "Range selection", Type:=8)

This line of code prompts the user to select a range containing Manager Names and their Job Titles. The InputBox function displays a dialog box that allows the user to select a range. The selected range is then assigned to the variable rng1.

Set rng2 = Application.InputBox("Select Range containing Salary Structure", "Range selection", Type:=8)

This line of code prompts the user to select a range containing the Salary Structure. The InputBox function displays a dialog box that allows the user to select a range. The selected range is then assigned to the variable rng2.

Set rng3 = Application.InputBox("Select output range", "Range selection", Type:=8)

For i = 1 To rng1.Rows.Count

This is a loop that iterates over each row in the rng1 range, which contains the Manager Names and Job Titles.

ManName = rng1.Cells(i, 2).Value

excel vba if iserror vlookup

These lines of code assign the value of the current Manager Name and Salary to the variables ManName and salary, respectively.

If IsError(Application.VLookup(ManName, rng2, 2, False)) Then
rng3.Cells(i, 1).Value = ""

This is a conditional statement that checks if the Manager’s Name exists in the rng2 range (which contains the Salary Structure). If the Manager’s Name is not found, then the output range cell for that Manager is set to blank.

Else

rng3.Cells(i, 1).Value = Application.VLookup(ManName, rng2, 2, False)

If the Manager Name is found in the rng2 range, then the output range cell for that Manager is set to the corresponding Salary based on the VLookup function.

Next i

This is the end of the loop. The code inside the loop will be executed for each row in the rng1 range.

End Sub

That’s the end of the subroutine. Overall, this code allows the user to select ranges of Manager Names, Job Titles, and Salary Structures, and an output range where the Salary for each Manager will be displayed. It then loops over each row in the Manager Name range, checks if the Manager Name exists in the Salary Structure range, and displays the corresponding Salary in the output range.

After entering the code, press the F5 or the Run command button as shown in the image below.

After pressing the Run button, you will be asked to enter the Manager’s Name and the Job Title.
We selected B5:C8
Press Enter after this.
Then you will be asked to input the range containing the Salary Structure of the Employees.
We selected E5:F7.
Press Enter after this.
In the Final step, you will be asked to enter the output range where the output result containing the salary of each employee will be present.
We select C11:C14.
Click OK after this.
After pressing OK, you will see that the range of cell C11:C14 is now filled with the Salary value of each employee.

The whole process is now shown in a single video below,

3. Determine Budget Based on Difficulty

In the below example, we are going to present the allocation of the budget based on the difficulty level in the range of cell C11:C14. The project name will be searched through the budget allocation structure presented in the range of cells G5:G8 and then return the budget allocation in the range of cells C11:C14.

The output will be something like the below image

Steps:

In the beginning, open the VBA editor window by pressing the shortcut key Alt+F11.
And then enter the following code in the code editor window.

Sub budget_alloation()
Set rng1 = Application.InputBox("Select Project Name and Difficulty level", _
"Range selection", Type:=8)
Set rng2 = Application.InputBox("Select range containing Budget Structure", _
"Range selection", Type:=8)
Set rng3 = Application.InputBox("Select Output range", "Range selection", Type:=8)
For i = 1 To rng1.Rows.Count
ManName = rng1.Cells(i, 2).Value
salary = rng2.Cells(i, 2).Value
If IsError(Application.VLookup(ManName, rng2, 2, False)) Then
rng3.Cells(i, 1).Value = ""
Else
rng3.Cells(i, 1).Value = Application.VLookup(ManName, rng2, 2, False)
End If
Next i
End Sub

Code Breakdown

Sub budget_alloation()

The Sub statement indicates the start of a subroutine, and the name of the subroutine is budget_allocation().

Set rng1 = Application.InputBox("Select Project Name and Difficulty level", _
"Range selection", Type:=8)

This line of code prompts the user to select a range containing Project Names and their Difficulty Level. The InputBox function displays a dialog box that allows the user to select a range. The preferred range is then assigned to the variable rng1.

Set rng2 = Application.InputBox("Select range containing Budget Structure", _
"Range selection", Type:=8)

This line of code prompts the user to select a range containing the budget structure. The InputBox function displays a dialog box that allows the user to select a range. The preferred range is then assigned to the variable rng2.

Set rng3 = Application.InputBox("Select Output range", "Range selection", Type:=8)

For i = 1 To rng1.Rows.Count

The For loop is used to iterate through each row in the range selected by the user in rng1.

        ManName = rng1.Cells(i, 2).Value
        salary = rng2.Cells(i, 2).Value

Within the loop, the variables ManName and salary are set to the values of the cells in the current row of rng1 and rng2, respectively.

If IsError(Application.VLookup(ManName, rng2, 2, False)) Then
rng3.Cells(i, 1).Value = ""
Else
rng3.Cells(i, 1).Value = Application.VLookup(ManName, rng2, 2, False)
End If

An If statement checks if the value of ManName can be found in the first column of rng2 using the VLookup function. If the value is not found, the cell in the output range corresponding to the current row in rng1 is set to an empty string.

If the value is found, the cell in the output range is set to the value in the second column of the row containing the matching value of ManName in rng2.

Next i

The Next statement marks the end of the For loop.

End Sub

The code appears to be incomplete or erroneous since it uses ManName and salary variables inconsistently, and the budget allocation calculation is missing. More information about the desired budget allocation process is needed to provide further assistance.

After entering the code, press the F5 or the Run command button as shown in the image below.

After running the code, you will be asked to enter the input range that will contain the project name and difficulty level.
We select C5:D8.
Press Enter after this.
Then another range input box asks for the Budget structure of the employees.
We enter F5:G8,
Press Enter.
Then enter the output value C11:C14 in the range box.
Click OK after this.
Then we will see that the budget of the projects is now present in the range of cell C11:C14

The whole process is now shown in the below video,

Issues Regarding Using If, IsError, and VLookup Together in Excel VBA

While using the VLookup function in combination with other functions, we sometimes face some weird problems and errors. There are multiple shades of reasons behind this, but only a handful are discussed below.

1. Avoid Worksheet Function in Excel

In some cases, the VLookup functions can throw errors like run time errors in Excel VBA. These Errors are most cases avoidable The main reason behind these errors is using the WorksheetFunction instead of the just Application option Like the code below,

Sub use_of_worksheet_function()
Output = Application.WorksheetFunction.VLookup(Range("A1"), Range("B1:C4"), 2, False)
Debug.Print Output
End Sub

Here if we use the worksheet function then if the value in cell A1 is not found in the B1:C4, there will be a runtime error. To resolve this issue, we can use only Application instead of the worksheet function as shown in the below code,

Sub use_of_worksheet_function()
Output = Application.VLookup(Range("A1"), Range("B1:C4"), 2, False)
Debug.Print Output
End Sub

If there is any issue in the dataset, then there is no run time error, but there will be a warning in the immediate window. You can always use the IsError in combination with the If function to avoid this kind of situation.

2. No Error Handling Used in Excel

If there is no error handling used, unwanted errors can disrupt the flow of the code performance. To avoid this type of problem, use error-handling operators like On Error…Go To.

If there is any error occurs, the code will jump to another location, thus avoiding the error and maintaining the flow of the code.

Sub use_of_worksheet_function()
On Error Resume Next
Output = Application.WorksheetFunction.VLookup(Range("A1"), Range("B1:C4"), 2, False)
Debug.Print Output
End Sub
‘In the previous code, the code will resume as usual if there is any error.
Sub use_of_worksheet_function()
On Error GoTo ErrorHandler
Output = Application.WorksheetFunction.VLookup(Range("A1"), Range("B1:C4"), 2, False)
ErrorHandler:
MsgBox "Error: " & Err.Description
Err.Clear
Debug.Print Output
End Sub

In the previous code, if there is any error occurring in the output part, the code will jump to the error handling portion where the code will show a message that there is an error in the code.

Users can easily modify this portion according to their needs.

3. Fault in Dataset or in Formula Assertion in Excel

In many cases, the primary reason why the VLookup function does not work and shows the error is because of the faulty dataset and faulty formula assertion.

For example, the VLookup function finds the data in another range of cells and returns desired table column values. Now if the data is not found in the target range of cells, then the code will return an error. And this data, not finding happens if there is a misplaced target date range.

Notice the dataset and the code below, Here we need to find the employee name and salary structure range of cells. If there is any match found, then the salary will be returned in the range of cell C11:C14. In that case, the target range of cells should be E5:F7.

But if in any case the code is altered and the wrong target range of cells is assigned, then there can be an error and faulty result.

In the below case, the user selected the F5:G7 instead of the E5:G7 which can lead to an empty output range as the input range is not able to find a suitable match in the E5:F7 range.

How to Use If with VLOOKUP in Excel Formula and VBA (A Simple Comparison)

1. Using Generic Formula

You can use a VLOOKUP function with an IF function. Combining these two functions can make you able to add another level of control inside the VLOOKUP function.

For example, we have the combination of the IF function with the VLOOKUP function.

In the below example, we determine whether a product value is greater than or less than 2 dollars or not.

We entered the following formula in the cell F6:

=IF(VLOOKUP(F5,$B$5:$D$9,3,FALSE)>=2,"Price >= $2.00","Price < $2.00")

2. Using VBA Macro

You can also use the below code in the code window to show whether the prices are greater than 2 dollars or not.

In the code editor, enter the following code and then run the code by pressing F5 or the Run command.

Sub IF_with_VLOOKUP()
If Application.VLookup(Range("F5"), Range("B5:D9"), 3, False) >= 2 Then
ActiveSheet.Cells(6, 6).Value = "The Product Price>=2$"
Else
ActiveSheet.Cells(6, 6).Value = "The Product Price <2$"
End If
End Sub

Frequently Asked Questions

1. How do I use VLOOKUP with ISERROR

Yes, you can use the VLOOKUP function in combination with the ISERROR Function. Using the ISERROR function will significantly reduce error-related issues. Notice the code below. Here using the VLOOKUP function we go results.

In the next image, we can see that we got the VLOOKUP functions coupled with the ISERROR functions. This ISERROR function protects the VLOOKUP function output from any kind of error output.

2. Can I use a VLOOKUP function with an IF function?

Yes, you can use a VLOOKUP function with an IF function. Combining these two functions can make you able to add another level of control inside the VLOOKUP function.

For example, we have the combination of the IF function with the VLOOKUP function.

In the below example, we determine whether a product value is greater than or less than 2 dollars or not.

We entered the following formula in the cell F6

=IF(VLOOKUP(F5,$B$5:$D$9,3,FALSE)>=2,"Price >= $2.00","Price < $2.00")

3. How do you Handle a VLOOKUP Error in VBA?

We can handle a VLookup Error in Excel VBA by various means.

The most useful way anyone can avoid this kind of error is to use the On Error Resume Next

On Error Resume Next
result = Application.WorksheetFunction.VLookup(lookup_value, Range("A1:B5"), 2, False)

Adding OnError Resume Next before the VLookup function made the code jump forward if there was any sign of error.

Another way is to use the Err. Number statement, Like in the below code.

If Err.Number <> 0 Then
MsgBox "Value not found"
Err.Clear
Else
MsgBox "Result: " & result
End If

Here we can evaluate if there is any error that happened already and then count them if the count is greater than 0 that means there is an error and then the code proceeds to show a message box showing that there is no Value found.

Otherwise, the code will proceed forward to show the intended result.

4. Can I Use VLOOKUP with Two Conditions?

You can certainly do VLOOKUP with two separate conditions or I can say lookup value. But there is some workaround to do this as you can’t do this directly with the VLOOKUP function alone. You need to use a helper column to assist you in this procedure.

Like below we want to use two separate criteria like both Project Name and Difficulty Level. With the conventional VLOOKUP function, you can not do this, for this, you have to create a helper column that will contain both the criteria column values. Then from the VLOOKUP function, you can use the helper column values as the input argument.

Download Practice Workbook

Download the following workbook to practice by yourself.

VBA Working with If, IsError, and VLookup Together.xlsx

Conclusion

In this whole article, we discussed how we can use the VLookup function alongside the IF and ISERRROR Excel VBA functions in detail. While at the same time giving answers to some common questions answer.

Get FREE Advanced Excel Exercises with Solutions!

Excel VBA: Working with If, IsError, and VLookup Together

Basic Syntax and Use of VBA If, IsError, and VLookup Functions

How to Launch VBA Editor in Excel

Is It Possible to Use IsError with VLookup in One Statement in Excel VBA?

Excel VBA If, IsError, and VLookup Functions: 3 Cases

1. Extracting Student Marks Who Failed from a List

2. Determine Employee Salary

3. Determine Budget Based on Difficulty

Issues Regarding Using If, IsError, and VLookup Together in Excel VBA

1. Avoid Worksheet Function in Excel

2. No Error Handling Used in Excel

3. Fault in Dataset or in Formula Assertion in Excel

How to Use If with VLOOKUP in Excel Formula and VBA (A Simple Comparison)

Frequently Asked Questions

Conclusion

Related Articles

Leave a reply Cancel reply