lookup then concatenate
 

Hi, I would like to lookup value in column A in column B, and return all
values in C(if value A is in B).

ie. Col A: abc
Col B Col C
abc z
bcd x
abc y

Result:
z: y

lookup then concatenate
 

If you download and install the free add-in Morefunc.xll then you can use
this array formula** to do what you want:

=TRIM(MCONCAT(IF(A1:A6="abc",B1:B6&" ","")))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Note that the result of this formula is limited to a total character length
of 255 characters including the space delimiters.

The add-in can be located he

http://www.download.com/Morefunc/300...-10423159.html

--
Biff
Microsoft Excel MVP


"Betty" wrote in message
...
Hi, I would like to lookup value in column A in column B, and return all
values in C(if value A is in B).

ie. Col A: abc
Col B Col C
abc z
bcd x
abc y

Result:
z: y