Array function at row2 to fill in the entire row is populating it in the same cell for few formulas. How to resolve this issue?

Question

I am creating a function which filter values from the leads generated and updated in the sheets. Formulas are used in A,E & G using values from other columns already available. It is working for simple formulas with only one column to refer, eg: =ARRAYFORMULA(IF(ISBLANK(B2:B),"","TRUE"))

Unfortunately creating issues for formulas involving more column references like the one below:

For implementing the following as an array function in A2: =IF(ISBLANK(B2),"",(if((C2=""), CONCATENATE(B2," Interest ",D2," ",G2),CONCATENATE(B2," Interest"," from ",C2," ",D2," ",G2))))

I tried to use this: =ARRAYFORMULA(IF(ISBLANK(B2:B),"",(if((C2:C=""), CONCATENATE(B2:B," Interest ",D2:D," ",G2:G),CONCATENATE(B2:B," Interest"," from ",C2:C," ",D2:D," ",G2:G)))))

It is populating all values in all the rows !

I know there's something wrong in what i am trying to do.

I have created a sample file here - Issue reference sheet which has two tabs, one with working function and the other one with errors with arrayformula, which i am trying to resolve with your help.

Support to resolve the same is much appreciated !

Thank you in advance!

Answer 1

Use & instead of CONCATENATE:

=ARRAYFORMULA(
   IF(B2:B="",,
    IF(C2:C="",B2:B&" Interest "&D2:D&" "&G2:G,
      B2:B&" Interest from "&C2:C&" "&D2:D&" "&G2:G)))