lookup then concatenate

  • Thread starter Thread starter Betty
  • Start date Start date
B

Betty

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
 
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 here:

http://www.download.com/Morefunc/3000-2077_4-10423159.html
 
Hello,

I have written a user defined function Cfreq for this kind of task:
http://www.sulprobil.com/html/cfreq.html

I would rather refrain from using an Excel addin like Morefunc because
you can never be sure whether its being maintained in future and you
have to rights at all as a user. Its top number 1 on my flop-list of
Excel Don'ts:
http://www.sulprobil.com/html/excel_don_ts.html
[Apologies if this software has become open source by now - but I
doubt this :-)]

Regards,
Bernd
 
Back
Top