TEXTJOIN returns values for all rows

[silverbluemoon]

Hi, all! I am certain there is something I am overlooking, so I am hoping someone here can offer a second pair of eyes to tell me what I'm doing wrong.

My reference values are in Col Q, from Q5:Q90, and my values to join together are in Col X, from X5:X90.

If I create this function:

=TEXTJOIN(", ",TRUE,IF($Q$5:$Q$90=$Q5,$X$5:$X$90,""))

I get the correct response:

1, 1, 1, 21, 2, 2, 4, 2, 4, 2, 4, 2, 4, 7

It is correct because the list grabs Col X only where Col Q continues to equal $Q5.

However, when I introduce another contingency via an additional IF statement, I also get values for all other rows in the range, incl. those where $Q$5:$Q$90<>$Q5.

In addition to the columns above, I introduce a Col W from W5:W90. My intent is to intercept a value of "Excellence" in Col W and just return a number "7", while any other values in Col X that do not have a value of "Excellence" in Col W on the same row should continue to return their actual values, as in the previous example.

Here's what I tried:

=TEXTJOIN("; ",TRUE,IF($Q$5:$Q$90=$Q5,IF($W$5:$W$90="Excellence",7,$X$5:$X$90)))

It works perfectly -- EXCEPT, I now get additional values of "FALSE" for all the other rows where Col Q does not equal $Q5.

1; 1; 1; 21; 2; 2, 4; 2, 4; 2, 4; 2, 4; 7; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE

As you can see, only the first 14 values match my expectations, and the rest are superfluous. I do not understand why this works flawlessly with one IF, but not with multiple. What am I doing wrong? How might I add additional contingencies without this happening?

Thank you in advance for your help!

 

[Fluff]

Try

=TEXTJOIN("; ",TRUE,IF($Q$5:$Q$90=$Q5,IF($W$5:$W$90="Excellence",7,$X$5:$X$90),""))

or maybe

=TEXTJOIN("; ",TRUE,IF($W$5:$W$90="Excellence",7,IF($Q$5:$Q$90=$Q5,$X$5:$X$90,"")))

 

[silverbluemoon]

Holy cow, @Fluff !! Thank you! This worked perfectly!!

 

[Fluff]

You're welcome & thanks for the feedback.