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.

**Table of Contents**Expand

**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
tab.*Developer* - Then, we will select the
command.*Visual Basic*

- Here, the
window will open.*Visual Basic* - After that, from the
option, we will choose the new*Insert*to write the VBA code.*Module*

**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 Sub
```

**VBA 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 Function
```

**VBA 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 Function
```

Finally, 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.