Require a lookup to return multiple values

[Guest]

Hi,

I am trying to get a lookup to return multiple text values.

My data looks like this:
Mike Bob Sue
Chicago x x
Cleveland x x
New York x
Miami x

and I want to summarize the data in another spreadsheet in 1 cell like:
Sue's Trips, Chicago, Miami.

I could do this in multiple columns but I want to avoid rows. I also hope
to avoid pivot tables because of the way my data is stored.

I have tried lookups and array formula's:

This array formulae is the closest I can get and will work to add numbers,
but it will not work for text cells. (returns only first instance or nothing)
=SUM(IF($F$59:$F$63="x",$E$59:$E$63,""))

Please help!

 

[Tom Ogilvy]

There isn't anything builtin that will do that. You can download Laurent
Longre's morefunc addin and it has a function or two that would do it I
believe.

http://longre.free.fr/english/

It has a help file that explains all the functions. You want one that will
contcatenate text strings in an array formula.

 

[Guest]

Thanks for all the help... through all your posts and some heavy research on
my part I came up with the solution I was looking for. For those of you who
are in the same boat here is my solution:

I borrowed the following code from http://www.mcgimpsey.com/ and entered it
in a VB module.

'*****************************************
'Purpose: Concatenate all cells in a range
'Inputs: rRng - range to be concatenated
' sDelimiter - optional delimiter
' to insert between cell Texts
'Returns: concatenated string
'*****************************************
Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelim & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function


Once in there, it allows me to use my array formula like the one below
perfectly. Where B6:B8 is my range containing my tags, and A6:A8 is the range
containing my text descriptions.

=(IF(B6:B8="x",MultiCat(A6:A8," ,"),""))

God love Mother Internet!!!!!

Thanks again,
Matt