How to Vlookup Multiple Values in Excel

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.

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.

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.

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, we will execute the IF. After that, we will resize the result value and assign it to 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, the result value in cell F9 is resized and the result value is transposed. Lastly, assign the code in cell F9 as “Not Found” and complete the process.

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.