SOLVED

TEXTJOIN AND not working

%3CLINGO-SUB%20id%3D%22lingo-sub-2293220%22%20slang%3D%22en-US%22%3ETEXTJOIN%20AND%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293220%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20why%20this%20is%20not%20working%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20do%20a%20TEXTJOIN%20for%20just%20VACANT%20rooms%20it%20works%20fine%2C%20but%20if%20I%20add%20in%20the%20AND%20function%20for%20both%20VACANT%20and%20Single%20it%20returns%20nothing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2CIF(AND(Current_staff%5BFull%20name%5D%3D%22VACANT%22%2CCurrent_staff%5BOccupancy%5D%3D%22Single%22)%2CCurrent_staff%5BOffice%2Fdesk%20number%5D%2C%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2293220%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293409%22%20slang%3D%22en-US%22%3ERe%3A%20TEXTJOIN%20AND%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293409%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20reply%2C%20but%20sadly%20it%20still%20does%20not%20work.%20Each%20of%20the%20IF%20statements%20will%20work%20on%20their%20own%2C%20the%20problem%20arises%20when%20I%20add%20in%20the%20AND%20function.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi All

 

Any ideas why this is not working please?

 

If I do a TEXTJOIN for just VACANT rooms it works fine, but if I add in the AND function for both VACANT and Single it returns nothing.

 

=TEXTJOIN(", ",TRUE,IF(AND(Current_staff[Full name]="VACANT",Current_staff[Occupancy]="Single"),Current_staff[Office/desk number],""))

4 Replies
Thank you for your reply, but sadly it still does not work. Each of the IF statements will work on their own, the problem arises when I add in the AND function.
best response confirmed by newbjohny (Occasional Contributor)
Solution

@newbjohny 

AND returns a single TRUE/FALSE value, not an array. Try

 

=TEXTJOIN(", ",TRUE,IF((Current_Staff[Full Name]="VACANT")*(Current_Staff[Occupancy]="Single"),Current_Staff[Office/desk number],""))

 

confirmed with Ctrl+Shift+Enter

Thank you, that worked. I've been going crazy over this for weeks.