Lookup returning multiple entries
 

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.

--
Best Regards,

Faraz

Lookup returning multiple entries
 

Could u Pls provide more detail with Example

or Email me with ur Sheet



"Faraz A. Qureshi" wrote:

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.

--
Best Regards,

Faraz

Lookup returning multiple entries
 

Data Table is as follows:

100 Value1
200 Value2
300 Value3
100 Value4
100 Value5
200 Value6
400 Value7

Looking up 300 would return "Value3", but 100 should return a result like:
"Value1/Value4/Value5"

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

Faraz


"Hardeep Kanwar" wrote:

Could u Pls provide more detail with Example

or Email me with ur Sheet



"Faraz A. Qureshi" wrote:

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.

--
Best Regards,

Faraz

Lookup returning multiple entries
 

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" wrote:

Data Table is as follows:

100 Value1
200 Value2
300 Value3
100 Value4
100 Value5
200 Value6
400 Value7

Looking up 300 would return "Value3", but 100 should return a result like:
"Value1/Value4/Value5"

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

Faraz


"Hardeep Kanwar" wrote:

Could u Pls provide more detail with Example

or Email me with ur Sheet



"Faraz A. Qureshi" wrote:

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.

--
Best Regards,

Faraz

Lookup returning multiple entries
 

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

Faraz


"Hardeep Kanwar" wrote:

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" wrote:

Data Table is as follows:

100 Value1
200 Value2
300 Value3
100 Value4
100 Value5
200 Value6
400 Value7

Looking up 300 would return "Value3", but 100 should return a result like:
"Value1/Value4/Value5"

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

Faraz


"Hardeep Kanwar" wrote:

Could u Pls provide more detail with Example

or Email me with ur Sheet



"Faraz A. Qureshi" wrote:

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.

--
Best Regards,

Faraz

Lookup returning multiple entries
 

Glad to help U

"Faraz A. Qureshi" wrote:

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

Faraz


"Hardeep Kanwar" wrote:

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" wrote:

Data Table is as follows:

100 Value1
200 Value2
300 Value3
100 Value4
100 Value5
200 Value6
400 Value7

Looking up 300 would return "Value3", but 100 should return a result like:
"Value1/Value4/Value5"

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

Faraz


"Hardeep Kanwar" wrote:

Could u Pls provide more detail with Example

or Email me with ur Sheet



"Faraz A. Qureshi" wrote:

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.

--
Best Regards,

Faraz

Lookup returning multiple entries
 

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

"Hardeep Kanwar" wrote:

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" wrote:

Data Table is as follows:

100 Value1
200 Value2
300 Value3
100 Value4
100 Value5
200 Value6
400 Value7

Looking up 300 would return "Value3", but 100 should return a result like:
"Value1/Value4/Value5"

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

Faraz


"Hardeep Kanwar" wrote:

Could u Pls provide more detail with Example

or Email me with ur Sheet



"Faraz A. Qureshi" wrote:

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.

--
Best Regards,

Faraz