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! :-)

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.

--
Regards,
Tom Ogilvy



"Matt" wrote in message
...
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! :-)