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!

 

[olimits7]

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.

This gets me back to my original formula, I'm basically trying to show 2 separate output of values; for example:

IF $R:$R equals "43" and $U:$U equals $B17, then value [Textjoin Example Output: AAA, BBB, CCC]
OR
IF $R:$R equals "41" and $U:$U equals $B17, then value [Textjoin Example Output: 111, 222, 333]

With current formula, I'm getting this Textjoin Example Output: [AAA, BBB, CCC, 111, 222, 333], which is not what I need.

Ty

 

[olimits7]

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

Oh ok, I didn't know that about 365; good to know!

 

[jasonb75]

If you need 2 outputs then you need 2 formulas, 1 for 43 and another for 41 (as @Fluff pointed out in post 6).

I think that the penny has just dropped, were you using the array confirmation across 2 cells to get one output in each? If so then this might allow the formula to spill dynamically (not tested).

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

 

[olimits7]

If you need 2 outputs then you need 2 formulas, 1 for 43 and another for 41 (as @Fluff pointed out in post 6).

I think that the penny has just dropped, were you using the array confirmation across 2 cells to get one output in each? If so then this might allow the formula to spill dynamically (not tested).

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

I just tried this but gives #N/A error. But what you wrote in {"43";"41"} , would this return values that equal both of these in a single cell (e.g. $A$1)?

If data equals "43", $A$1 would show only AAA, BBB, CCC textjoin data
If data equals "41", $A$1 would show only 111, 222, 333 textjoin data

Ty.

 

[olimits7]

The below works properly if I add each formula to a separate cell but I need to have this show within 1 cell, either show output for "41" or output for "43", not both at the same time.

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

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

Ty.

 

[jasonb75]

either show output for "41" or output for "43", not both at the same time.

How are we to know which one should be shown at any given time?

 

[olimits7]

How are we to know which one should be shown at any given time?

Oh ok, I see what you mean; that's why I was trying to wrap this around around an IF statement, but maybe I need to have the data setup like the following and then I could use an IF statement wrapped around?

$B17= Customer # (e.g. AB123)
$C17= 41 or 43 (depending on data)

For example:
IF $B17=AB123 AND $C17 = 41, THEN "TEXTJOIN W/ 41 FORMULA", IF $B17=AB123 AND $C17 = 43, THEN "TEXTJOIN W/ 43 FORMULA" - Would something like this work?

 

[Fluff]

How about

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

 

[olimits7]

How about

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

Ok, what you wrote does work...Ty. I'm just trying to modify it slightly because in my current pivot table I'm not showing the "41", "43" values instead I have the following which translates to these values.

In $E17 it shows either "PROJECT" for "43" or "PARTNER" for "41"; so I tried updating your formula to the following below but now getting an #N/A error. $AB:$AB is where this text is saved in the source data.

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

Ty.

 

[Fluff]

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