how to return mulitple corresponding values

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then, based on the sample data you posted...

Assuming this data is in the range A2:B12. You have the unique groups listed
in the range E2:E5.

Enter this array formula** in F2 and copy down to F5:

=SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12,"")&" "))," ","^")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Melanie" <Melanie DeleteThis @discussions.microsoft.com> wrote in message
news:ABFB33D5-BD15-4BF1-A155-78CAFD6390C2@microsoft.com...
> Yes, I'm definitely interested. I know in reality that it can't be
> unlimited,
> but I need at least 10 names appended. I'll also have to figure out how to
> move anything over 30 characters to the next line, but first things first.
>
> Can this function handle multiple reoccurring group names (i.e. loop
> within
> a loop)?
>
> group name
> marketing mjagger
> marketing rthomas
> marketing xbono
> accounting rcharles
> accounting jbrown
> hr jmayer
> it jjohnson
> it bdylan
> it jjoplin
> it akiedis
> it braitt
>
> output:
>
> mjagger^rthomas^xbono
> rcharles^jbrown
> jmayer
> jjohnson^bdylan^jjoplin^akiedis^braitt
>
> The looping seems to be the limitation I'm running into with the index
> function or I'm using it incorrectly.
>
> "T. Valko" wrote:
>
>> >There can be...unlimited names for a group.
>>
>> Well, that's not good! You have to narrow down "unlimited".
>>
>> There is a free add-in available that has a function that will do this
>> but
>> the resulting string is *limited* to no more than 255 characters. So,
>> that
>> means "tvalko^debih^biff" can't be more than 255 chars.
>>
>> Are you interested in this?
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Melanie" <Melanie DeleteThis @discussions.microsoft.com> wrote in message
>> news:5387AA2C-4128-42DC-B106-473C08814235@microsoft.com...
>> >I have reoccuring group names in column A and multiple names (i.e
>> >tvalko,
>> > debih, biff, etc.) in column B for each group. There can be one name or
>> > unlimited names for a group. I want to identify the name s for each
>> > different
>> > group and obtain the list on one line (tvalko^debih^biff) with carats
>> > as
>> > delimeters.
>> >
>> > "T. Valko" wrote:
>> >
>> >> You need to be more specific and provide some details.
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Melanie" <Melanie DeleteThis @discussions.microsoft.com> wrote in message
>> >> news:3ABEB001-A560-4217-8E00-BA8C2DC11E3B@microsoft.com...
>> >> > I'm also trying to do this. I need to lookup a value in one column
>> >> > that
>> >> > returns multiple values in the second column, but I want to list it
>> >> > out
>> >> > with
>> >> > "^" between the values. I need to do this dynamically for multiple
>> >> > lookup
>> >> > values.
>> >> >
>> >> > Can you help me?
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> >> You can do that, however, since the formula returns possible
>> >> >> multiple
>> >> >> results for each lookup value you'd have to use another formula
>> >> >> (the
>> >> >> same
>> >> >> one, just change =$A$60 to the next cell reference).
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "Debi H" <DebiH DeleteThis @discussions.microsoft.com> wrote in message
>> >> >> news:E422362F-91DD-4B85-AFD3-031DD09DD3E8@microsoft.com...
>> >> >> >I would like to copy down and do this for all the values in the
>> >> >> >list
>> >> >> >not
>> >> >> >just
>> >> >> > the value from A60
>> >> >> >
>> >> >> > "Biff" wrote:
>> >> >> >
>> >> >> >> > =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2:$A$15)),ROW(1:1)),5)
>> >> >> >>
>> >> >> >> Use this:
>> >> >> >>
>> >> >> >> =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2:$A$15)-ROW(A$2)+1),ROWS($1:1)),5)
>> >> >> >>
>> >> >> >> > can I change $A$2:$A$15=$A$60 to look up a range?
>> >> >> >>
>> >> >> >> What do you mean?
>> >> >> >>
>> >> >> >> Biff
>> >> >> >>
>> >> >> >> "Debi H" <DebiH DeleteThis @discussions.microsoft.com> wrote in message
>> >> >> >> news:FBB32FD3-988E-4880-884A-47292E533784@microsoft.com...
>> >> >> >> > One more question please....
>> >> >> >> >
>> >> >> >> > If the fromula:
>> >> >> >> > =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2:$A$15)),ROW(1:1)),5)
>> >> >> >> >
>> >> >> >> > can I change $A$2:$A$15=$A$60 to look up a range?
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > "Biff" wrote:
>> >> >> >> >
>> >> >> >> >> You're welcome!
>> >> >> >> >>
>> >> >> >> >> Biff
>> >> >> >> >>
>> >> >> >> >> "Debi H" <DebiH DeleteThis @discussions.microsoft.com> wrote in message
>> >> >> >> >> news:4F4E1A11-934B-44CB-B284-45774695BA73@microsoft.com...
>> >> >> >> >> > That worked...thanks
>> >> >> >> >> >
>> >> >> >> >> > "Biff" wrote:
>> >> >> >> >> >
>> >> >> >> >> >> Type the formula then, instead of hitting ENTER like you
>> >> >> >> >> >> normally
>> >> >> >> >> >> would,
>> >> >> >> >> >> hold down both the CTRL key AND the SHIFT key then hit
>> >> >> >> >> >> ENTER.
>> >> >> >> >> >> When
>> >> >> >> >> >> done
>> >> >> >> >> >> properly Excel will enclose the formula in squihhly braces
>> >> >> >> >> >> { }.
>> >> >> >> >> >> You
>> >> >> >> >> >> can't
>> >> >> >> >> >> just type these braces in, you MUST use the key
>> >> >> >> >> >> combination
>> >> >> >> >> >> to
>> >> >> >> >> >> produce
>> >> >> >> >> >> them.
>> >> >> >> >> >>
>> >> >> >> >> >> If you're still having problems I'll be glad to look at
>> >> >> >> >> >> your
>> >> >> >> >> >> file
>> >> >> >> >> >> and
>> >> >> >> >> >> see
>> >> >> >> >> >> if
>> >> >> >> >> >> I can figure it out. Just let me know how to contact you.
>> >> >> >> >> >>
>> >> >> >> >> >> Biff
>> >> >> >> >> >>
>> >> >> >> >> >> "Debi H" <DebiH DeleteThis @discussions.microsoft.com> wrote in
>> >> >> >> >> >> message
>> >> >> >> >> >> news:F4426697-D07A-46FE-9166-EB3CA9665853@microsoft.com...
>> >> >> >> >> >> > That still does not work for me. Am I missing
>> >> >> >> >> >> > something?
>> >> >> >> >> >> > I did the key stroke of (CTRL+SHIFT+ENTER) 1ST
>> >> >> >> >> >> > Entered the formula
>> >> >> >> >> >> > and I get the VALUE error
>> >> >> >> >> >> >
>> >> >> >> >> >> >
>> >> >> >> >> >> >
>> >> >> >> >> >> > "Biff" wrote:
>> >> >> >> >> >> >
>> >> >> >> >> >> >> Hi!
>> >> >> >> >> >> >>
>> >> >> >> >> >> >> The basic formula is something like this:
>> >> >> >> >> >> >>
>> >> >> >> >> >> >> Entered as an array using the key combo of
>> >> >> >> >> >> >> CTRL,SHIFT,ENTER:
>> >> >> >> >> >> >>
>> >> >> >> >> >> >> =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW($1:$10)),ROW(1:1)))
>> >> >> >> >> >> >>
>> >> >> >> >> >> >> Then copy down.
>> >> >> >> >> >> >>
>> >> >> >> >> >> >> Where column A contains the lookup_value and column B
>> >> >> >> >> >> >> contains
>> >> >> >> >> >> >> the
>> >> >> >> >> >> >> values
>> >> >> >> >> >> >> to
>> >> >> >> >> >> >> be returned.
>> >> >> >> >> >> >>
>> >> >> >> >> >> >> Need more specific details to offer a more robust
>> >> >> >> >> >> >> suggestion.
>> >> >> >> >> >> >>
>> >> >> >> >> >> >> Biff
>> >> >> >> >> >> >>
>> >> >> >> >> >> >> "MetricsShiva" <MetricsShiva DeleteThis @discussions.microsoft.com>
>> >> >> >> >> >> >> wrote
>> >> >> >> >> >> >> in
>> >> >> >> >> >> >> message
>> >> >> >> >> >> >> news:7CB57B18-A2B0-43D2-A726-6650586A3B94@microsoft.com...
>> >> >> >> >> >> >> >i want to look up a name that occurs several times in
>> >> >> >> >> >> >> >one
>> >> >> >> >> >> >> >column
>> >> >> >> >> >> >> >of a
>> >> >> >> >> >> >> > spreadsheet and return corresponding values from each
>> >> >> >> >> >> >> > row
>> >> >> >> >> >> >> > the
>> >> >> >> >> >> >> > name
>> >> >> >> >> >> >> > occurs
>> >> >> >> >> >> >> > on.
>> >> >> >> >> >> >> > Vlookup returns only one value. How can I get
>> >> >> >> >> >> >> > multiple
>> >> >> >> >> >> >> > values?
>> >> >> >> >> >> >>
>> >> >> >> >> >> >>
>> >> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Thanks! That worked! Although, for some reason, it wouldn't let me reference
the unique list (col E) from a pivot table in another worksheet so I
referenced the unique list using = in the same worksheet and for some reason
that worked(?). I'm just happy it works! I've been working on this on-and-off
for a week.

"T. Valko" wrote:

> Download and install the free Morefunc.xll add-in from:
>
> http://xcell05.free.fr/morefunc/english/index.htm
>
> Then, based on the sample data you posted...
>
> Assuming this data is in the range A2:B12. You have the unique groups listed
> in the range E2:E5.
>
> Enter this array formula** in F2 and copy down to F5:
>
> =SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12,"")&" "))," ","^")
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Melanie" <Melanie RemoveThis @discussions.microsoft.com> wrote in message
> news:ABFB33D5-BD15-4BF1-A155-78CAFD6390C2@microsoft.com...
> > Yes, I'm definitely interested. I know in reality that it can't be
> > unlimited,
> > but I need at least 10 names appended. I'll also have to figure out how to
> > move anything over 30 characters to the next line, but first things first.
> >
> > Can this function handle multiple reoccurring group names (i.e. loop
> > within
> > a loop)?
> >
> > group name
> > marketing mjagger
> > marketing rthomas
> > marketing xbono
> > accounting rcharles
> > accounting jbrown
> > hr jmayer
> > it jjohnson
> > it bdylan
> > it jjoplin
> > it akiedis
> > it braitt
> >
> > output:
> >
> > mjagger^rthomas^xbono
> > rcharles^jbrown
> > jmayer
> > jjohnson^bdylan^jjoplin^akiedis^braitt
> >
> > The looping seems to be the limitation I'm running into with the index
> > function or I'm using it incorrectly.
> >
> > "T. Valko" wrote:
> >
> >> >There can be...unlimited names for a group.
> >>
> >> Well, that's not good! You have to narrow down "unlimited".
> >>
> >> There is a free add-in available that has a function that will do this
> >> but
> >> the resulting string is *limited* to no more than 255 characters. So,
> >> that
> >> means "tvalko^debih^biff" can't be more than 255 chars.
> >>
> >> Are you interested in this?
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Melanie" <Melanie RemoveThis @discussions.microsoft.com> wrote in message
> >> news:5387AA2C-4128-42DC-B106-473C08814235@microsoft.com...
> >> >I have reoccuring group names in column A and multiple names (i.e
> >> >tvalko,
> >> > debih, biff, etc.) in column B for each group. There can be one name or
> >> > unlimited names for a group. I want to identify the name s for each
> >> > different
> >> > group and obtain the list on one line (tvalko^debih^biff) with carats
> >> > as
> >> > delimeters.
> >> >
> >> > "T. Valko" wrote:
> >> >
> >> >> You need to be more specific and provide some details.
> >> >>
> >> >> --
> >> >> Biff
> >> >> Microsoft Excel MVP
> >> >>
> >> >>
> >> >> "Melanie" <Melanie RemoveThis @discussions.microsoft.com> wrote in message
> >> >> news:3ABEB001-A560-4217-8E00-BA8C2DC11E3B@microsoft.com...
> >> >> > I'm also trying to do this. I need to lookup a value in one column
> >> >> > that
> >> >> > returns multiple values in the second column, but I want to list it
> >> >> > out
> >> >> > with
> >> >> > "^" between the values. I need to do this dynamically for multiple
> >> >> > lookup
> >> >> > values.
> >> >> >
> >> >> > Can you help me?
> >> >> >
> >> >> > "Biff" wrote:
> >> >> >
> >> >> >> You can do that, however, since the formula returns possible
> >> >> >> multiple
> >> >> >> results for each lookup value you'd have to use another formula
> >> >> >> (the
> >> >> >> same
> >> >> >> one, just change =$A$60 to the next cell reference).
> >> >> >>
> >> >> >> Biff
> >> >> >>
> >> >> >> "Debi H" <DebiH RemoveThis @discussions.microsoft.com> wrote in message
> >> >> >> news:E422362F-91DD-4B85-AFD3-031DD09DD3E8@microsoft.com...
> >> >> >> >I would like to copy down and do this for all the values in the
> >> >> >> >list
> >> >> >> >not
> >> >> >> >just
> >> >> >> > the value from A60
> >> >> >> >
> >> >> >> > "Biff" wrote:
> >> >> >> >
> >> >> >> >> > =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2:$A$15)),ROW(1:1)),5)
> >> >> >> >>
> >> >> >> >> Use this:
> >> >> >> >>
> >> >> >> >> =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2:$A$15)-ROW(A$2)+1),ROWS($1:1)),5)
> >> >> >> >>
> >> >> >> >> > can I change $A$2:$A$15=$A$60 to look up a range?
> >> >> >> >>
> >> >> >> >> What do you mean?
> >> >> >> >>
> >> >> >> >> Biff
> >> >> >> >>
> >> >> >> >> "Debi H" <DebiH RemoveThis @discussions.microsoft.com> wrote in message
> >> >> >> >> news:FBB32FD3-988E-4880-884A-47292E533784@microsoft.com...
> >> >> >> >> > One more question please....
> >> >> >> >> >
> >> >> >> >> > If the fromula:
> >> >> >> >> > =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2:$A$15)),ROW(1:1)),5)
> >> >> >> >> >
> >> >> >> >> > can I change $A$2:$A$15=$A$60 to look up a range?
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> > "Biff" wrote:
> >> >> >> >> >
> >> >> >> >> >> You're welcome!
> >> >> >> >> >>
> >> >> >> >> >> Biff
> >> >> >> >> >>
> >> >> >> >> >> "Debi H" <DebiH RemoveThis @discussions.microsoft.com> wrote in message
> >> >> >> >> >> news:4F4E1A11-934B-44CB-B284-45774695BA73@microsoft.com...
> >> >> >> >> >> > That worked...thanks
> >> >> >> >> >> >
> >> >> >> >> >> > "Biff" wrote:
> >> >> >> >> >> >
> >> >> >> >> >> >> Type the formula then, instead of hitting ENTER like you
> >> >> >> >> >> >> normally
> >> >> >> >> >> >> would,
> >> >> >> >> >> >> hold down both the CTRL key AND the SHIFT key then hit
> >> >> >> >> >> >> ENTER.
> >> >> >> >> >> >> When
> >> >> >> >> >> >> done
> >> >> >> >> >> >> properly Excel will enclose the formula in squihhly braces
> >> >> >> >> >> >> { }.
> >> >> >> >> >> >> You
> >> >> >> >> >> >> can't
> >> >> >> >> >> >> just type these braces in, you MUST use the key
> >> >> >> >> >> >> combination
> >> >> >> >> >> >> to
> >> >> >> >> >> >> produce
> >> >> >> >> >> >> them.
> >> >> >> >> >> >>
> >> >> >> >> >> >> If you're still having problems I'll be glad to look at
> >> >> >> >> >> >> your
> >> >> >> >> >> >> file
> >> >> >> >> >> >> and
> >> >> >> >> >> >> see
> >> >> >> >> >> >> if
> >> >> >> >> >> >> I can figure it out. Just let me know how to contact you.
> >> >> >> >> >> >>
> >> >> >> >> >> >> Biff
> >> >> >> >> >> >>
> >> >> >> >> >> >> "Debi H" <DebiH RemoveThis @discussions.microsoft.com> wrote in
> >> >> >> >> >> >> message
> >> >> >> >> >> >> news:F4426697-D07A-46FE-9166-EB3CA9665853@microsoft.com...
> >> >> >> >> >> >> > That still does not work for me. Am I missing
> >> >> >> >> >> >> > something?
> >> >> >> >> >> >> > I did the key stroke of (CTRL+SHIFT+ENTER) 1ST
> >> >> >> >> >> >> > Entered the formula
> >> >> >> >> >> >> > and I get the VALUE error
> >> >> >> >> >> >> >
> >> >> >> >> >> >> >
> >> >> >> >> >> >> >
> >> >> >> >> >> >> > "Biff" wrote:
> >> >> >> >> >> >> >
> >> >> >> >> >> >> >> Hi!
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >> The basic formula is something like this:
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >> Entered as an array using the key combo of
> >> >> >> >> >> >> >> CTRL,SHIFT,ENTER:
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >> =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW($1:$10)),ROW(1:1)))
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >> Then copy down.
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >> Where column A contains the lookup_value and column B
> >> >> >> >> >> >> >> contains
> >> >> >> >> >> >> >> the
> >> >> >> >> >> >> >> values
> >> >> >> >> >> >> >> to
> >> >> >> >> >> >> >> be returned.
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >> Need more specific details to offer a more robust
> >> >> >> >> >> >> >> suggestion.
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >> Biff
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >> "MetricsShiva" <MetricsShiva RemoveThis @discussions.microsoft.com>
> >> >> >> >> >> >> >> wrote
> >> >> >> >> >> >> >> in
> >> >> >> >> >> >> >> message
> >> >> >> >> >> >> >> news:7CB57B18-A2B0-43D2-A726-6650586A3B94@microsoft.com...
> >> >> >> >> >> >> >> >i want to look up a name that occurs several times in
> >> >> >> >> >> >> >> >one
> >> >> >> >> >> >> >> >column
> >> >> >> >> >> >> >> >of a
> >> >> >> >> >> >> >> > spreadsheet and return corresponding values from each
> >> >> >> >> >> >> >> > row
> >> >> >> >> >> >> >> > the
> >> >> >> >> >> >> >> > name
> >> >> >> >> >> >> >> > occurs
> >> >> >> >> >> >> >> > on.
> >> >> >> >> >> >> >> > Vlookup returns only one value. How can I get
> >> >> >> >> >> >> >> > multiple
> >> >> >> >> >> >> >> > values?
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >>
> >> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>