concatenate in array formula?
 

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?

--
Squirl Girl

concatenate in array formula?
 

On Nov 5, 4:13*pm, Squirl Girl wrote:
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?

--
Squirl Girl

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 he
http://www.cpearson.com/Excel/stringconcatenation.aspx