Textjoin With Multiple "And" Conditions/Criteria?

[olimits7]

Hi,

I'm trying to use a TEXTJOIN with multiple "and" criteria/conditions, I almost got it to work with the following formula but it shows "FALSE" in the text output instead of just being blank. For example, "FALSE, M12345, M45678, M99584, etc.".

={TEXTJOIN(", ",TRUE,UNIQUE(IF('YTD Transactions'!$U:$U=$B17,IF(LEFT('YTD Transactions'!$R:$R,2)="43",'YTD Transactions'!$AG:$AG),"")))}

I'm trying to setup 2 different TEXTJOIN's; one where $R:$R equals "43" and the other where it equals "41", and also matches to whatever value is in $B17 under $U:$U.

I also tried this but gives me a #VALUE error.

={TEXTJOIN(", ",TRUE,UNIQUE(IF(('YTD Transactions'!$U:$U=$B17)*(LEFT('YTD Transactions'!$R:$R,2)="41"),'YTD Transactions'!$AG:$AG),""))}

Thank you!

 

[jasonb75]

Third to last closing bracket is in the wrong place, it should be after the null string.

I would expect a #CALC error if no rows meet the filter criteria, but perhaps one or the other functions overrides that to #N/A

 

[olimits7]

You should only get #N/A with that formula if col AG has that error on one of the rows that match the criteria.

Ok, you're right...I used an IFNA formula to make those #N/A show up as "" and now the formula shows a #VALUE error. Would the blanks be causing this to show as #VALUE now?

 

[olimits7]

The $AB column contains a formula which shows either "PARTNER" or "PROJECT" depending on the lookup, I'm not sure if this is causing the #VALUE issue?

 

[jasonb75]

Would the blanks be causing this to show as #VALUE now?

No, that would be the mistake that I pointed out in post 21.

 

[olimits7]

No, that would be the mistake that I pointed out in post 21.

Oh ok, all the brackets look correct in the following formula; so I'm not sure why I'm getting VALUE error still.

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER('YTD Transactions'!$AG:$AG,('YTD Transactions'!$U:$U=$B16)*('YTD Transactions'!$AB:$AB=$E16)),""))

 

[Fluff]

Get rid of the ,"" at the end of the formula

 

[olimits7]

Oh yeah, I see the bracket issue now; I ended up changing to this and now it works!

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER('YTD Transactions'!$AG:$AG,('YTD Transactions'!$U:$U=$B16)*('YTD Transactions'!$AB:$AB=$E16),"")))

Thank you for your help with this! @Fluff @jasonb75

 

[jasonb75]

As @Fluff has pointed out, you don't need the ,"" part, but without it you will still get an error if there is nothing that meets the criteria.