lookup then concatenate

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
 
T

T. Valko

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
 
B

Bernd P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top