The VLookup function is typically used to find a value in the table’s leftmost column and will return a value from the specified column in the same row. With some practical examples and illustrations, this tutorial will show you how you can use Excel VBA Vlookup with Multiple Criteria.
How to Open VBA Macro Editor in Excel
VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to perform Vlookup with multiple criteria in Excel. Therefore, you can follow the simple steps to open the VBA editor.
Steps:
- Firstly, we will open the Developer tab.
- Then, we will select the Visual Basic command.
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the new Module to write the VBA code.
Excel VBA Vlookup with Multiple Criteria: 3 Examples
This article will demonstrate how to perform Vlookup with multiple criteria in Excel by giving 3 practical examples with explanations. Here, we will apply the VBA worksheet function, then create a user-defined function for two and three different criteria, and finally, create a UserForm to show all the criteria of an employee.
Here, we will create our sample dataset, including the ID, Name, Department, and Income of different employees. So, you can go through a sample video of our work on VBA Vlookup with multiple criteria in Excel.
Example 1: Using VBA WorksheetFunction to Vlookup with Multiple Criteria
In this section, we will demonstrate a data set where we have included some employees’ names, IDs, departments, and income. Our goal is to show the individual income of all the employees by using the VBA VLookup function.
Now, in this section, we will generate the following VBA code in the Module by opening the new Module which we have discussed in the above section on how to launch the VBA Macro Editor. Then, copy the following code and paste it into the above Module. After that, click on Run to see the output.
Code:
Sub Show_Income()
'Using For Loop
For j = 4 To Cells(Rows.Count, "C").End(xlUp).Row
Cells(j, "B").Value = Cells(j, "D").Value & "_" & Cells(j, "E").Value
Next j
'Declaring Variables
Dim Employee_Name As String
Dim Dept As String
Dim Name_Department As String
Dim Income As Long
'Using InputBox to enter the employee name and department
Employee_Name = InputBox("Write the name of the Employee")
Dept = InputBox("Select the Department")
Name_Department = Employee_Name & "_" & Dept
On Error GoTo Direction
check:
'Applying VBA VLookup function
Income = Application.WorksheetFunction.VLookup(Name_Department, Range("B4:F15"), 5, False)
MsgBox ("The Income of the Employee is " & Income)
Direction:
If Err.Number = 1004 Then
MsgBox ("Nothing")
End If
End SubVBA Breakdown
Sub Show_Income()- Firstly, the code begins with the user-defined name ShowIncome(), which when called will carry out a series of operations.
For j = 4 To Cells(Rows.Count, "C").End(xlUp).Row
Cells(j, "B").Value = Cells(j, "D").Value & "_" & Cells(j, "E").Value
Next j- The worksheet’s rows are iterated by using the For loop, which begins at row 4 and ends at the last non-empty row in column C. The code adds the values from columns D and E together for each row and then puts the result in column B.
Dim Employee_Name As String
Dim Dept As String
Dim Name_Department As String
Dim Income As Long- The Dim command is then used to declare three variables: Employee_Name as String, Dept as String, and Income as Long. Later in the code, these variables will be applied.
Employee_Name = InputBox("Write the name of the Employee")
Dept = InputBox("Select the Department")
Name_Department = Employee_Name & "_" & Dept
On Error GoTo Direction- The user is prompted to enter the name of an employee and choose a department using the InputBox function. The variables Employee_Name and Dept are each assigned the values entered.
- The concatenated value of Employee_Name and Dept is put into the variable Name_Department.
- The following code block uses the On Error GoTo statement to handle any errors that might arise.
Income = Application.WorksheetFunction.VLookup(Name_Department, Range("B4:F15"), 5, False)
MsgBox ("The Income of the Employee is " & Income)
Direction:
If Err.Number = 1004 Then
MsgBox ("Nothing")
End If- The output of the VLookup function, which looks for the value of Name_Department in the range B4:F15 and returns the value in the fifth column of the corresponding row, is set to the Income variable.
- The code jumps to the Direction label and determines whether the error code is equivalent to 1004 if the VLookup function returns an error. If so, a message box with the word “Nothing” is displayed by the code.
- The code shows a message box with the text “The Income of the Employee is” and the value of the Income variable if the VLookup function is successful.
End Sub- Finally, this VBA macro ends with an End statement.
For doing this dynamic income representation, we will apply the VBA VLookup WorksheetFunction. Now, you can follow the above video for a better understanding, as it contains all the steps.
Example 2: Creating a User-Defined Function to Vlookup with Multiple Criteria in Excel VBA
Case 1: Vlookup with Two Criteria
Here, we will create a User-Defined function using VBA and show the income of an employee based on two criteria.
In this section, we will generate the following VBA code for a user-defined function named VLOOKUP_TwoCriteria in the Module by opening the new Module which we discussed in the above section on how to launch a VBA macro editor. Then, copy the following code and paste it into the above Module. After that, click on Run to see the output.
Code:
Function VLOOKUP_TwoCriteria(lookup_value As Variant, lookup_range As Range, _
criteria1 As Variant, criteria2 As Variant, return_col As Integer)
'Declaring Variables
Dim i As Long
'Using For Loop
For i = 1 To lookup_range.Rows.Count
'Using If statement
If lookup_range.Cells(i, 1) = criteria1 And lookup_range.Cells(i, 2) = criteria2 Then
VLOOKUP_TwoCriteria = lookup_range.Cells(i, return_col)
Exit Function
End If
Next i
VLOOKUP_TwoCriteria = "#N/A"
End FunctionVBA Breakdown
Function VLOOKUP_TwoCriteria(lookup_value As Variant, lookup_range As Range, _
criteria1 As Variant, criteria2 As Variant, return_col As Integer)- Here, we will create a function named Function procedure VLOOKUP_TwoCriteria, which is a user-defined function that will return a value when it is performed. This function has five arguments: lookup_value, lookup_range, criteria1, criteria2, and return_col.
Dim i As Long
'Using For Loop
For i = 1 To lookup_range.Rows.Count- Now, we declare a variable for using it in For Loop. The For Loop iterates from lookup_range, starting from row 1 and ending at the last row in the given range.
If lookup_range.Cells(i, 1) = criteria1 And lookup_range.Cells(i, 2) = criteria2 Then
VLOOKUP_TwoCriteria = lookup_range.Cells(i, return_col)
Exit Function
End If
Next i- To determine whether the values in the first and second columns of the current row of the lookup range match those of criteria1 and criteria2, respectively, by using the If statement. If the values match, the function uses the Exit Function statement to leave and assigns the value of the cell in the return column of the current row to the VLOOKUP_TwoCriteria variable. The function moves on to the next row of the lookup range if the values are not the same.
VLOOKUP_TwoCriteria = "#N/A"- The function sets the VLOOKUP_TwoCriteria variable to the value “#N/A” at the end of the For loop if there is no match.
End Function- The End Function statement indicates the conclusion of the function operation.
Therefore, we apply the User-Defined function here to find the income of an employee based on two criteria. Now we want to know Robert’s income as a developer by applying the following formula in cell I6. Therefore, you will see Robert’s income living in Texas as a Developer in the above So, for better understanding, follow the steps properly.
=VLOOKUP_TwoCriteria(G5,B4:E15,H5,I5,4)
Case 2: Vlookup with Three Criteria
Here, we will create a User-Defined function using VBA and show the income of an employee based on three criteria.
If you want to know the three criteria of VLOOKUP then you will follow this. Here, we will create a function named Function procedure VLOOKUP_ThreeCriteria, which is a user-defined function that will return a value when it is performed. This function has five arguments: lookup_value, lookup_range, criteria1, criteria2, criteria3, and return_col. Then, copy the following code and paste it into the above Module. After that, click on Run to see the output.
Code:
Function VLOOKUP_ThreeCriteria(lookup_value As Variant, lookup_range As Range, _
criteria1 As Variant, criteria2 As Variant, criteria3 As Variant, return_col As Integer)
'Declaring Variables
Dim i As Long
'Using For Loop
For i = 1 To lookup_range.Rows.Count
'Using If statement
If lookup_range.Cells(i, 1) = criteria1 And lookup_range.Cells(i, 2) = criteria2 And _
lookup_range.Cells(i, 3) = criteria3 Then
VLOOKUP_ThreeCriteria = lookup_range.Cells(i, return_col)
Exit Function
End If
Next i
VLOOKUP_ThreeCriteria = "#N/A"
End FunctionFinally, we apply the User-Defined function here to find the income of an employee based on three criteria. Now we want to know Robert’s income as a developer by applying the following formula in cell I6. Therefore, you will see Robert’s income from living in Texas as a Developer and input his ID in the I6 cell. So, for better understanding, follow the steps properly.
=VLOOKUP_ThreeCriteria(H5,B4:F15,I5,J5,K5,5)
Read More: Excel VBA to Vlookup Values for Multiple Matches
Example 3: Creating an UserForm to Vlookup with Multiple Criteria in Excel VBA
Here, we create a UserForm that contains a ComboBox and four TextBoxes. The ComboBox lists names, and when a name is selected, the TextBoxes display information associated with the name from a worksheet named “Sheet2“. The VLookup function looks up all the information with the selected name as the lookup value and the data range from column B to column F.
Here, we will create a UserForm to design our work smoothly. So, opening the VBA Editor, you can select the Insert option and click the UserForm to open it.
Then, copy the following code and paste it into the above Module. After that, click on Run to see the output.
Code:
Private Sub ComboBox1_Change()
'Declaring Variable
Dim i
'Using CountA worksheet function
i = Application.WorksheetFunction.CountA(Sheet2.Range("B:B"))
'Using For Loop
For j = 1 To 4
Me("Textbox" & j).Value = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Sheet2.Range("B" & 2, "F" & i), j + 1, 0)
Next j
End Sub
Private Sub Label2_Click()
End Sub
Private Sub UserForm_Initialize()
Me.ComboBox1.RowSource = "Name"
End Sub
VBA Breakdown
Private Sub ComboBox1_Change()
'Declaring Variable
Dim i
'Using CountA worksheet function
i = Application.WorksheetFunction.CountA(Sheet2.Range("B:B"))Code Breakdown
- Firstly, we will define the first sub-procedure.
"ComboBox1_Change()".- Secondly, we declare a variable “i“. Then, it counts the number of cells that contain data in column B of Sheet2 using the COUNTA worksheet function and assigns the value to “i“.
For j = 1 To 4
Me("Textbox" & j).Value = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Sheet2.Range("B" & 2, "F" & i), j + 1, 0)
Next j- The For loop runs for four iterations. Each iteration uses the VLOOKUP function to look up the selected name in the data range. It returns the value in the column that corresponds to the current iteration plus one (starting with column C). The value is then assigned to the corresponding TextBox (named “Textbox1” to “Textbox4“).
Private Sub UserForm_Initialize()
Me.ComboBox1.RowSource = "Name"- After that, we will define a sub-procedure “UserForm_Initialize()“.
- It sets the RowSource property of the ComboBox to “Name“, which means that the ComboBox gets its list of names from a named range called “Name“.
End Sub- Finally, this VBA macro ends with an End statement.
So, for better understanding, follow this above video where we have shown the detailed process.
Read More: How to Use Excel VBA VLookup with Named Range
How to Use If Else Statement to Vlookup with Multiple Criteria in Excel VBA
Moreover, we will demonstrate a data set where we have included some writers’ names. To show you examples, here we have introduced a Birth Place column to the author lists dataset. We aim to find the birthplace of each writer that is listed in the “Birth_Place” sheet. Our goal is to show the birthplace of each writer by using the VBA VLookup Function.
Now, in this section, we will generate the following VBA code in the Module by opening the new Module which we discussed in the above section on how to launch the VBA macro editor. In this method, we will use the If..Then..Else statement for Excel VBA Vlookup in another worksheet. Then, copy the following code and paste it into the above Module. After that, click on Run to see the output.
Code:
Sub Finding_birthplace()
'Declaring Variables
Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim Finding_birthplaceAs Variant
'Setting sheets in variables
Set ws_1 = Worksheets("Birth_place")
Set ws_2 = Worksheets("VBA")
On Error Resume Next
Finding_birthplace = Application.WorksheetFunction.VLookup(ws_2.Range("B5"), _
ws_1.Range("B5:C11"), 2, False)
On Error GoTo 0
'Using If Else statement
If (IsEmpty(Finding_birthplace)) Then
ws_2.Range("E5").Formula = CVErr(xlErrNA)
Else
ws_2.Range("E5").Value = Finding_birthplace
End If
End Sub
VBA Breakdown
Sub Finding_birthplace()Code Breakdown
- The Sub procedure Finding_birthplace, it will locate the birthplace of a person by searching for their name in a table.
Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim Finding_birthplaceAs Variant- Secondly, the Dim statement is used to declare three variables: ws_1 and ws_2 as Worksheet objects, and Finding_birthplace as a Variant data type.
Set ws_1 = Worksheets("Birth_place")
Set ws_2 = Worksheets("VBA")- Now, we apply the Set statement to assign the Birth_place and VBA worksheets to the ws_1 and ws_2 variables, respectively.
On Error Resume Next- Then, by using the On Error Resume Next statement, we ignore any runtime errors that may occur during the implementation of the macro.
Finding_birthplace = Application.WorksheetFunction.VLookup(ws_2.Range("B5"), _
ws_1.Range("B5:C11"), 2, False)- After that, we set the Finding_birthplace variable to assign the result of the VLookup function, which finds the value in cell B5 of the VBA worksheet in the table located in the Birth_place worksheet’s B5:C11 range. The function returns the value in the second column of the table (column C) that corresponds to the matching value in column B.
On Error GoTo 0- Here, we apply the, On Error GoTo 0 statement to turn off the error handling.
If Else statement
If (IsEmpty(Finding_birthplace)) Then
ws_2.Range("E5").Formula = CVErr(xlErrNA)
Else
ws_2.Range("E5").Value = Finding_birthplace
End If- Therefore, we apply the If Else statement to check if the Finding_birthplace variable is empty. If it is, the code assigns the #N/A error value to cell E5 of the VBA worksheet using the CVErr function. If it is not empty, the code assigns the value of the Finding_birthplace variable to cell E5.
End Sub- Finally, the Sub procedure ends with the End Sub statement.
Finally, you can follow the above video for a better understanding, as it contains all the steps.
How to Use XLOOKUP Function with Multiple Criteria in Excel
Firstly, you can use certain functions if you need to look for values based on several different criteria in a huge spreadsheet. One of the functions for looking up values based on various criteria is the XLOOKUP function. I’ll describe Excel’s XLOOKUP with many criteria in this article. So, go through the following steps. First, select the cell where you want to place your resultant value. Here, I selected cell H5. Then, type the following formula in the selected cell.
Finally, you will get Robert’s income as a developer.
=XLOOKUP(1,B5:B13=F5)*(C5:C13=G5),D5:D13)
How to Use Ampersand Operator to Join Multiple Criteria in VLOOKUP in Excel
In the below data set, we have shown some employee names and their department with their respective salary. Here, column B represents a helper column which is the combination of the C and D columns.
- As the VLOOKUP function looks for a value in the first column, we have to keep this helper column in the first place in the following table. By using an ampersand (&), the VLOOKUP function in this example will look for a text value in column G which is the combination of an employee and his corresponding department.
- Follow the steps properly. Now we want to know Robert’s income as a developer by applying the following formula in cell H5. Therefore, you will see Robert’s income as a developer in the above image.
=VLOOKUP(G5&G6,B5:E15,4,0)
Frequently Asked Questions
- How can I lookup numerous values in Excel VBA?
To get numerous values in a single cell, we can generate a customized function in VBA that is very similar to the VLOOKUP function, and it checks each cell in a column to find the lookup value.
- How can I use VLOOKUP to combine several criterion selections?
Follow these 3 steps to set up multiple criteria. VLOOKUP: Concatenate (join) values from columns you want to use for your criteria in a helper column. Configure VLOOKUP to use a table with the helpful column as a reference. The table’s first column must be the helper column.
Things to Remember
- In Excel VBA, the syntax of the VLookup function remains unchanged.
- When the VBA VLookup function cannot find any lookup value, it shows a 1004 error.
- If the VLookup function returns an error, we can handle it with a GoTo statement.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it yourself.
Conclusion
In this article, we’ve covered 3 examples of how to use Excel VBA VLOOKUP with multiple criteria. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Further Readings
- Use Excel VBA VLOOKUP to Find Values in Another Worksheet
- How to Use Excel VBA VLookup Within Loop
- Excel VBA to Vlookup in Another Workbook Without Opening
- Excel VBA: Working with If, IsError, and VLookup Together
