Merge an array of concatenated columns in Excel

Question

Given the Excel values in columns A-C and this formula in column D =CONCAT(B2,":",C2):

I extract and combine all matching rows in another tab using the formula =TEXTJOIN(", ",TRUE,IF(E2=AVL!A:A,AVL!D:D,"")) with the following result:

I would like to skip the formula in Column D and simply concatenate in the TEXTJOIN() formula.

I imagine there is a formula that would allow me to CONCAT() the columns, then join the related rows, but I haven't been able to find it.

Any suggestions?

I have tried:

• =TEXTJOIN(", ",TRUE,IF(E239=AVL!A:A,CONCAT(AVL!B:B,":",AVL!C:C),"")), which results in a #CALC error

• =TEXTJOIN(", ",TRUE,CONCAT(IF(E240=AVL!A:A,AVL!B:B,""),":",IF(E240=AVL!A:A,AVL!C:C,""))), which results in all of B, then all of C

Answer 1

From the context of the OP, it seems you are trying to achieve something like this,

• Formula used in cell F13

=TEXTJOIN({" : ";", "},1,FILTER(B:C,E13=A:A,"Not Found"))

---

Note: Make sure to change the cell references and ranges accordingly as per your suit. Also suggested to use absolute references, instead of using the whole range, which includes iteration of unwanted blank cells, thus slowing down the working functionality of Excel Engine. One better way is to convert the source into Structured References aka Tables, next to use the formulas.

---