I have one twist that i should have mentioned before.

There's always a forgotten twist! <g

I'm hoping that the values in the cells don't already contain spaces and/or
commas!

Try something like this (split up so the needed space characters don't get
taken out by line wrap).

All on one line:

=SUBSTITUTE(TRIM(VLOOKUP(A1,B:F,2,0)&" "&
VLOOKUP(A1,B:F,3,0)&" "&
VLOOKUP(A1,B:F,4,0)&" "&
VLOOKUP(A1,B:F,5,0))," ",", ")


--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
Thanks for the reply. It is what i wanted. I have one twist that i should
have mentioned before. Not all of the columns have information in them
hence
the formula below shows a small space and then comma when there is no
information(some of the data have two or three commas at the end) . How do
i
make these not appear when there is no data????

"T. Valko" wrote:

look at four columns of data in spreadsheet 1 and return
into one column in spreadsheet 2 separated by commas.

Concatenate the lookups into one formula. Something like this:

=VLOOKUP(A1,B:F,2,0)&","&VLOOKUP(A1,B:F,3,0)&","&V LOOKUP(A1,B:F,4,0)&","&VLOOKUP(A1,B:F,5,0)

--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
I am using two spreadsheets and need to return multiple cells into one
cell.

I have been using vlookup to return single results and using surnames
as
the
common cell in both spreadsheets. I want to continue using the surname
to
link to the other spreadsheet but look at four columns of data in
spreadsheet
1 and return into one column in spreadsheet 2 separated by commas.

I hope this makes sense.

Thanks