Vlookup Multiple Values

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.

• Initially, select cell C16 to solve this issue.

Method 2: Applying VLOOKUP Function with Helper Column

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

• 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.

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.

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.

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.

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.

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.

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

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

• 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.

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.