Lookup returning multiple entries

[Faraz A. Qureshi]

If a value is appearing once in the lookup array, I want operation of a
simple Vlookup, however, if an entry appears twice or more times how to have
a multiple return like:
Value1/Value2/Value3

Thanx in advance.

 

[Hardeep Kanwar]

Hi! Faraz

Use this UDF

Function VlookupAll(rLookupVal, rTable As Range, lCol As Long)

Dim rCell As Range, Result

VlookupAll = CVErr(xlErrNA)

For Each rCell In rTable
If rCell = rLookupVal Then
Result = Result & "/" & rCell.Offset(, lCol - 1)
End If
Next rCell

If Result <> "" Then
Result = Right(Result, Len(Result) - 1)
VlookupAll = Result
End If

End Function


Enter this Formula in Any Blank Cell

Lookup Value=100

=vlookupall(A2,A11:B2,2)

Hardeep Kanwar

 

[Faraz A. Qureshi]

Unbelievably XCLent!

 

[Hardeep Kanwar]

Glad to help U

Unbelievably XCLent!
--
Do check "Yes" if this post is helpful,
Best Regards,

Faraz

 

[stevedemo77]

Hardeep,
Is there a way to edit this code so each value is inserted in the next row
below rather than all in one cell with / between each value?

I have a summary sheet where i want to list all names that belong to a
certain city, coming from a table on a different worksheet. The city name is
in column A, the person's name in column B. I want to be able to return all
names to the summary sheet.

Thanks,
Steve