The Excel VLOOKUP function by default allows you to find only a single match and will return the corresponding row of a selected column value. What if you want to find VLOOKUP multiple matches, not just the first one? In this post let us explore this more complicated scenario. Instead of VLOOKUP however we will use INDEX and MATCH.

Using the MATCH Function in Excel

To find the first MATCH of the “A” value in column B:B we use the following formula as shown on the image below:

=MATCH(E$1,$B$2:$B$10,0)

See the scenario below:

Finding multiple matches in Excel

Now say we want to find all matches of “A” in column B:B as seen below.

Below the formulas in cells E2-E4. In E2 we find the corresponding row of the first “A”, then in subsequenty (E3-E4) we look for the rows of the next found “A”. You can drag this formula down as much times as needed.

'E2
=IFERROR(MATCH(E$1,$B$2:$B$10,0),"")

'E3 (drag this down)
=IFERROR(MATCH(E$1,OFFSET($B$2,E2,0,ROWS($B2:$B$10)-E2),0)+E2,"")

'E4 (dragged from E3)
=IFERROR(MATCH(E$1,OFFSET($B$2,E3,0,ROWS($B3:$B$10)-E3),0)+E3,"")

VLOOKUP Multiple Matches

To do a multiple match VLOOKUP we simply need to expand on the above Multiple MATCH example and add the INDEX function like so:

'E2
=IFERROR(INDEX($A$2:$A$10,MATCH(F$1,$B$2:$B$10,0)),"")
'E3
=IFERROR(INDEX($A$2:$A$10,MATCH(F$1,OFFSET($B$2,F2,0,ROWS($B2:$B$10)-F2),0)+F2),"")
'E4 (simply drag the E3 formula down)
=IFERROR(INDEX($A$2:$A$10,MATCH(F$1,OFFSET($B$2,F3,0,ROWS($B3:$B$10)-F3),0)+F3),"")

Using VBA to do a VLOOKUP Multi Match

In case you want a more sophisticated approach to doing a multi match INDEX MATCH / VLOOKUP you can also use the VBA Dictionary to record all instances of all lookup values along with selected columns. A simple version of this approach can be found in my post about using VLOOKUP in VBA. Below, however, I expanded this example by using a VBA Collection inside the VBA Dictionary to store value associated with each match of every lookup value (basically creating a very simply tree-like structure).

Based on the “A1:B10” table above the VBA code below will create my dictionary dict object.

Dim dict As Object, col As Collection, item As Variant
    Set dict = CreateObject("Scripting.Dictionary")
    'Column B:B has my lookup values
    For Each r In Range("B:B").SpecialCells(xlCellTypeConstants)
        'Omit row with headers
        If r.Row = 1 Then GoTo NextR
        
        If dict.Exists(r.Value) Then
            Set col = dict(r.Value)
        Else
            Set col = New Collection
            'Column A:A has the values I want to find for each lookup
            Call dict.Add(r.Value, col)
        End If
        col.Add Range("A" & r.Row)
NextR:
    Next r

After creating the dictionary I can now print all values from column “A:A” for any value of column “B:B”:

For Each item In dict("A")
   Debug.Print item
Next item

'Result:
'2
'4
'7