# 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?

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