| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Returning Multiple Values in a lookup | Excel Worksheet Functions | |||
| returning multiple cells for a single lookup | Excel Discussion (Misc queries) | |||
| Returning multiple indices for a lookup | Excel Worksheet Functions | |||
| Lookup Returning Multiple Results | Excel Discussion (Misc queries) | |||
| Returning multiple instances of the same lookup value | Excel Worksheet Functions | |||
Linear Mode
