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!

 

[Fluff]

Does col R have 43 or is that just the 1st 2 characters in the cell?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[olimits7]

In column R, 43 is just the 1st 2 characters; it really shows "430001" and "410001".

Ok, thanks...I updated my account information.

 

[jasonb75]

First one is missing a blank / null string for thr fist IF, second one the blank / null string is in UNIQUE, not the FALSE argument of IF, although if you have UNIQUE then you should have FILTER, so I would use that instead of IF.

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

Personally, I would use dynamic ranges for this, not entire columns.

 

[olimits7]

First one is missing a blank / null string for thr fist IF, second one the blank / null string is in UNIQUE, not the FALSE argument of IF, although if you have UNIQUE then you should have FILTER, so I would use that instead of IF.

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

Personally, I would use dynamic ranges for this, not entire columns.

Oh ok, FILTER seems to work but then how to I use FILTER to show the different set of values when it equals "41" instead?

Ty.

 

[Fluff]

Just change the 43 in the formula to 41

 

[olimits7]

Just change the 43 in the formula to 41

But I need both conditions to be met in a single formula; for example, show values when it equals 41 and also show different set of values when it equals 43?

 

[olimits7]

I tried this but doesn't seem to calculate properly.

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

 

[jasonb75]

Perhaps this one,

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER('YTD Transactions'!$AG:$AG,('YTD Transactions'!$U:$U=$B17)*((LEFT('YTD Transactions'!$R:$R,2)="43")+(LEFT('YTD Transactions'!$R:$R,2)="41")),"")))

There are a few ways to interpret what you asked for so I've gone with the most common one.

 

[Fluff]

Firstly you do not need to use Ctrl Shift Enter on 365.
Secondly I'm afraid I don't understand what you are after.