concatenate in array formula?


S

Squirl Girl

I have an array formula that sums cells in column C if their corresponding
label in column B matches a desired string in cell A1.

{=SUM(IF($A$1=$B$1:$B$9,$C$1:$C$9,0))}

It works fine. But if I try to concatenate instead of sum (using
Ctrl+Shift+Enter), this fails:

{=CONCATENATE(IF($A$1=$B$1:$B$3,$D$1:$D$3,""))}

Evaluating the formula yields
CONCATENATE(IF({FALSE,TRUE,FALSE},$D$1:$D$3,""))
which evaluates to CONCATENATE(""), even though I got one match, and cell D2
contains the string "West".

Can concatenation be done in an array formula?
 
Ad

Advertisements

S

Spiky

I have an array formula that sums cells in column C if their corresponding
label in column B matches a desired string in cell A1.

{=SUM(IF($A$1=$B$1:$B$9,$C$1:$C$9,0))}

It works fine.  But if I try to concatenate instead of sum (using
Ctrl+Shift+Enter), this fails:

{=CONCATENATE(IF($A$1=$B$1:$B$3,$D$1:$D$3,""))}

Evaluating the formula yields
CONCATENATE(IF({FALSE,TRUE,FALSE},$D$1:$D$3,""))
which evaluates to CONCATENATE(""), even though I got one match, and cellD2
contains the string "West".

Can concatenation be done in an array formula?

Not with the built-in functions. A UDF could do it. I think I've seen
such a UDF posted here. Otherwise, there are sets of UDFs on the web
that could be downloaded for free. Morefunc includes one called
MCONCAT, or look here:
http://www.cpearson.com/Excel/stringconcatenation.aspx
 

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