In this article, we will learn how to look up multiple values in Excel. Here we will use The **VLOOKUP** function and other functions to execute the process. The **VLOOKUP** function can’t look up multiple data at a time by default. So, we will use different functions to solve this issue in Excel. Here we will use **IFERROR, SMALL, INDEX MATCH,** and the Nested **VLOOKUP** functions. Extracting multiple values from a huge dataset is very efficient and saves time. Also, this operation helps analyze data and provide accurate reporting.

**Vlookup Multiple Values in Excel: 3 Methods**

Here, we will use the **VLOOKUP** function in Excel. As only the **VLOOKUP** function doesn’t extract multiple values by default, we will use other functions with the VLOOKUP function.

### Method 1: Using COUNTIF and VLOOKUP Functions to Vlookup Multiple Values

Now, we will use **the COUNTIF function **and **The VLOOKUP function** values in Excel.

- Initially select cell
**C5**to add sales person number.

`=D5&COUNTIF($D$5:D5,D5)`

- Initially, select cell
**C16**to solve this issue.

`=VLOOKUP(C14&C15,$C$5:$E$12,3,0)`

### Method 2: Applying VLOOKUP Function with Helper Column

- Initially, select cell
**C5**to add the helper column and enter the formula below.

`=D5&""&E5`

- Then enter the value of any helper column in cell
**C14**and look up the product value of the helper. - Select cell
**C15**and enter the formula to extract the value of the product.

`=VLOOKUP(C14,$C$5:$E$12,3,0)`

### Method 3: Using VLOOKUP Function in Both Rows and Columns

Here we will use the **VLOOKUP** function and **the MATCH function **in Excel to extract the values using both the row and column as lookup ranges.

- Select cell
**C17**to enter the formula and extract the value from both rows and columns.

`=VLOOKUP(C15,B6:H13, MATCH(C16, B5:H5, 0), FALSE)`

**Formula Breakdown**

**MATCH(C16, B5:H5, 0)**

- This part of the formula returns the values as true in cell
**C16**if there is a match in the range**B5:H5**.

**VLOOKUP(C15,B6:H13, MATCH(C16, B5:H5, 0), FALSE)**

- The formula will return the value from
**March**if the value matches the range**B6:H13**.

**How to Vlookup Multiple Values Without VLOOKUP Function in Excel: 5 Methods**

Here we will learn how to execute this process without using **the VLOOKUP function** in Excel.

### Method 1: Vlookup Multiple Values in a Column

In this method, we will use **the IFERROR function, the INDEX function, the SMALL function, **and **the ROW function** to execute this process.

- In the beginning, select cell
**F5**to enter the formula and extract multiple values in a column.

`=IFERROR(INDEX($D$5:$D$12, SMALL(IF(F$4=$C$5:$C$12, ROW($D$5:$D$12)-4,""), ROW()-4)),"")`

**Formula Breakdown**

**ROW($D$5:$D$12)-4,””), ROW()-4)**

- This part will return the value on the
**5th**row and**5th**column from the dataset regarding the range**D5:D12**.

**IF(F$4=$C$5:$C$12, ROW($D$5:$D$12)-4,””), ROW()-4))**

- This part will Extract the value from the range
**C5:C12**regarding cell**F4**.

**SMALL(IF(F$4=$C$5:$C$12, ROW($D$5:$D$12)-4,””), ROW()-4)**

- The
**SMALL**function will extract the smallest value from the array using the**IF**function.

**(INDEX($D$5:$D$12, SMALL(IF(F$4=$C$5:$C$12, ROW($D$5:$D$12)-4,””), ROW()-4)),””)**

- This part will return the formula as an array if the lookup values match the function; otherwise, it will return
**#ERROR.**

**IFERROR(INDEX($D$5:$D$12, SMALL(IF(F$4=$C$5:$C$12, ROW($D$5:$D$12)-4,””), ROW()-4)),””)**

- The formula will return the value if the lookup value matches the formula; otherwise, it will return blank.

### Method 2: Vlookup Multiple Values in a Row

- Here we will look up multiple values in cell
**G5**using the same process. But instead of using the**ROW**function both times, here we will use**the COLUMN function**to look up multiple values in a row.

`=IFERROR(INDEX($D$5:$D$12, SMALL(IF($F5=$C$5:$C$12, ROW($D$5:$D$12)-4,""), COLUMN()-6)),"")`

### Method 3: Vlookup Multiple Values for Multiple Criteria

In this method, we will complete the process for multiple criteria in cell **H5**. Here, we will use the same functions as earlier.

`=IFERROR(INDEX($D$5:$D$12, SMALL(IF(1=((--($F$6=$C$5:$C$12)) * (--($G$6=$B$5:$B$12))), ROW($D$5:$D$12)-4,""), ROW()-4)),"")`

### Method 4: Applying INDEX and MATCH Functions to Vlookup Multiple Values

In this method, we will use the** INDEX** and** MATCH** functions to complete the process

- Select cell
**C16**and enter the formula below.

`=INDEX(D5:D12, MATCH(1, (C14=C5:C12) * (C15=B5:B12), 0))`

**Formula Breakdown**

**MATCH(1, (C14=C5:C12) * (C15=B5:B12)**

- This part will return the value as true if both ranges
**C5:C12**and**B5:B12**return the value from**C14**and**C15**.

**INDEX(D5:D12, MATCH(1, (C14=C5:C12) * (C15=B5:B12), 0))**

- The formula will return the value if the index range matches the formula; otherwise, it will return
**N/A**.

### Method 5: Using VBA Macro to Vlookup Multiple Values

In this method, we will use a **VBA Macro **to solve this issue in Excel.

- Initially, go to
**Developer >> Visual Basic**and open the VBA window. - Select
**Insert >> Module**to write the code and execute the process.

**Code**

```
Sub VLookupSalesData()
Dim LookupRange As Range
Dim Criteria As String
Dim Result() As Variant
Dim FoundRow As Range
Dim i As Long
Set LookupRange = Range("B5:E12")
Criteria = Range("G5").Value
For Each FoundRow In LookupRange.Columns(2).Cells
If FoundRow.Value = Criteria Then
ReDim Preserve Result(i)
Result(i) = FoundRow.Offset(0, 1).Value
i = i + 1
End If
Next FoundRow
If i > 0 Then
Range("F9").Resize(i).Value = WorksheetFunction.Trans_
pose (Result)
Else
Range("F9").Value = "Not found"
End If
End Sub
```

**Code Breakdown**

```
Sub VLookupSalesData()
Dim LookupRange As Range
Dim Criteria As String
Dim Result() As Variant
Dim FoundRow As Range
Dim i As Long
```

- This code is named as
**VLookupSalesData**where the lookup range and**FoundRow**are declared as**Range,**Criteria is declared as**String**, Result is declared as an**array**and i as**Long**.

```
Set LookupRange = Range("B5:E12")
Criteria = Range("G5").Value
For Each FoundRow In LookupRange.Columns(2).Cells
If FoundRow.Value = Criteria Then
ReDim Preserve Result(i)
Result(i) = FoundRow.Offset(0, 1).Value
i = i + 1
```

- Here, the lookup range is
**B5:B12**and the lookup value is cell**G5**. The code will start from column**B**and find the value from column B. If the criteria match, then we will execute the**IF**After that, resize the result value and assign the value in one column right of the current column.

```
End If
Next FoundRow
If i > 0 Then
Range("F9").Resize(i).Value = WorksheetFunction.Trans_
pose (Result)
Else
Range("F9").Value = "Not found"
End If
End Sub
```

End the IF statement if the value matches the function and move on to the loop where the value of **i** is greater than **Zero (0)**. Then resize the result value in cell **F9 **and transpose the result value. Lastly, assign the code in cell **F9** as **“Not Found”** and complete the process.

- Here is the final output in the
**Output**

**How to Use Nested VLOOKUP Function to Vlookup Multiple Values in Excel**

Here we will use the nested **VLOOKUP** function to execute the process in Excel in cell **I5**.

`=VLOOKUP(VLOOKUP(H5, $B$5:$C$12, 2, FALSE), $E$5:$F$12, 2, FALSE)`

**Formula Breakdown**

**VLOOKUP(H5, $B$5:$C$12, 2, FALSE)**

- In this part, the function will return the value of the
**Outlet**if the lookup range matches the lookup value.

**VLOOKUP(VLOOKUP(H5, $B$5:$C$12, 2, FALSE), $E$5:$F$12, 2, FALSE)**

- Here, the output of
**the VLOOKUP function**will be used as the lookup range in the second**VLOOKUP**function which will return a value if the formula matches the lookup range.

**Frequently Asked Questions**

**Q1: Can you VLOOKUP multiple values at once?**

**Ans:** There is no way to get multiple values at once using the **VLOOKUP** function. You can use the nested **VLOOKUP** function or the **INDEX **and **MATCH **functions.

**Q2: What can I use instead of VLOOKUP for multiple values?**

**Ans:** You can use the **INDEX MATCH **function instead of the **VLOOKUP** function for multiple values.

**Q3: Which is better VLOOKUP or INDEX-MATCH?**

**Ans:** **INDEX-MATCH** is better than the **VLOOKUP** function in Excel.

**Things to Remember**

- The
**VLOOKUP**function does not return multiple values by default. So, we need to use another part to look up multiple values, as already shown. - Always set up the correct range while using the function, and try to use the absolute range using the
**($)**

**Download Practice Workbook**

You may download the below workbook for practice.

**Conclusion**

Here, we learned how to look up multiple values in Excel with the **VLOOKUP** function and without the **VLOOKUP** function. Here we also used the **INDEX MATCH, IFERROR, SMALL, **and **ROW **functions. If there are multiple criteria, then we used the Nested **VLOOKUP** function and VBA macro to solve the problems. We covered every solution in this article. So, Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions.