Vlookup with multiple returns

Hi Erik,

Try using below formula:-

Formula:

=IFERROR(INDEX($B$1:$B$9,SMALL(IF($A$1:$A$9=$F$1,ROW($A$1:$A$9),""),ROW($A1))),"")

Enter this using ctrl shift enter key combination.

See attached:- multiple lookup values.xlsx


Regards,
DILIPandey

<click on below 'star' if this helps>

a vlookup will only return the very 1st match it find. so unless you can find some1 to add something to the values in A to make the duplicates unique, then I would suggest that you maybe take a look at using filters?

It’s not possible to get the matching data with concatenated result using a single cell worksheet function. So try the below UDF

Function Clookup(LValue As Range, LRng As Range, ResRng As Range) As String
Dim i As Long

If WorksheetFunction.CountIf(LRng, LValue.Value) Then
    For i = 1 To LRng.Cells.Count
            If LRng.Cells(i).Value = LValue.Value Then
                Clookup = Clookup & ResRng.Cells(i).Value & ", "
            End If
    Next i
End If

Clookup = Trim(Clookup)

If Len(Clookup) > 0 Then
    Clookup = Left(Clookup, Len(Clookup) - 1)
End If

End Function

=CLookup(LookupValue,LookupRange,ResultRange)

In C1 cell

Formula:

=clookup($A1,$A$1:$A$9,$B$1:$B$9)

Drag it down.

Refer the attached excel for details.