# Vlookup Multiple Values

Get FREE Advanced Excel Exercises with Solutions!

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

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

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.

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 (\$)

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  