Lookup returning multiple entries

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

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.
 
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
 
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
 
Back
Top