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.