Using an Array Function with TEXTJOIN to concatenate multiple cells, but with multiple criteria?

Question

I have found that the following formula works perfectly, as long as I only use one criteria.

{=TEXTJOIN("; ", 1, IF(A2:A9="USA", C2:C9, ""))}

Result: "Apples; Crust; Flour; Chocolate"

But, how do I add multiple criteria. For example, I've tried the following:

{=TEXTJOIN("; ", 1, IF(AND(A2:A9="USA", B2:B9="Cake"), C2:C9, ""))}

Desired Result: "Flour; Chocolate"

But, this formula pulls up no matches. Is there a way to get 2+ criteria to work with this Array TEXTJOIN function?

possible duplicate of [TEXTJOIN for xl2010/xl2013 with criteria](https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050?s=1|82.5783#50719050). — , Oct 24 '18 at 00:16

score 2 · Answer 1 · answered Oct 23 '18 at 22:53

2

I figured it out moments after posting this. The answer is to use a nested IF, instead of an AND function inside one IF.

Example:

{=TEXTJOIN("; ", 1, IF(A2:A9="USA", IF(B2:B9="Cake",C2:C9, ""),""))}

answered Oct 23 '18 at 22:53

MrMusAddict

I do not have the most recent version of Excel, so I don't have any way of testing this, but instead of nested IF statements, is it possible to use a `MATCH` statement in an array formula to return all the results, not just the first one? Using this as part of a formula: `MATCH("USA"&"CAKE",A2:A9&B2:B9,0)` – jeranon Oct 23 '18 at 23:42
@jeranon, what would be the point? Even if you got it working, the results would be identical and you are adding to the calculation load. – Oct 24 '18 at 00:15
@Jeeped I'm thinking it would be less calculation steps than a nested IF statement, and if there is less calculation steps, it would scale a lot friendlier on a macro scale. – jeranon Oct 24 '18 at 00:18
No, it would be the same or more. The same if all rows matched, more if there were any non-matches. A nested IF(...IF(... 'short-circuits' meaning the second is not calculated if the first fails. With your method, both are always calculated. – Oct 24 '18 at 00:24
@jeeped thanx! I appreciate the explanation. – jeranon Oct 24 '18 at 19:04

1 Answers1