Textjoin IF giving all

[dmj120]

I am using textjoin with an if statement that is also using indirect for the tab names.

I've tried playing around, but this forumula just isn't working.
C3 == {=TEXTJOIN(" ",TRUE,(IF(INDIRECT(TEXT($C$2,"Mmmm")&"!$H$3:$H$500")=B4,INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3:$F$500"),"")))}

I have also tried watching a few videos but cannot figured out why all the matching items are listed instead of just the unique items from F3:F500 that equal cell B4. Could it be due to column H3:H500 has duplicate entries as well?

I'm using 2019, so I don't have the unique formula.

 

[Peter_SSs]

cannot figured out why all the matching items are listed instead of just the unique items from F3:F500 that equal cell B4

As you said, you do not have the UNIQUE function and there is nothing in your current formula to identify unique items.
Try this, still entered as an array formula, instead.

=TEXTJOIN(" ",TRUE,(IF(INDIRECT(TEXT($C$2,"Mmmm")&"!$H$3:$H$500")=B4,IF(MATCH(B4&"|"&INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3:$F$500"),INDIRECT(TEXT($C$2,"Mmmm")&"!$H$3:$H$500")&"|"&INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3:$F$500"),0)=ROW(INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3:$F$500"))-ROW(INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3"))+1,INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3:$F$500"),""),"")))

 

[dmj120]

As you said, you do not have the UNIQUE function and there is nothing in your current formula to identify unique items.
Try this, still entered as an array formula, instead.

=TEXTJOIN(" ",TRUE,(IF(INDIRECT(TEXT($C$2,"Mmmm")&"!$H$3:$H$500")=B4,IF(MATCH(B4&"|"&INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3:$F$500"),INDIRECT(TEXT($C$2,"Mmmm")&"!$H$3:$H$500")&"|"&INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3:$F$500"),0)=ROW(INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3:$F$500"))-ROW(INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3"))+1,INDIRECT(TEXT($C$2,"Mmmm")&"!$F$3:$F$500"),""),"")))

Thanks Peter!! Works perfectly

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.