TEXTJOIN AND not working

[newbjohny]

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],""))

[#2]

Hi there,
I think the following link can be useful:

https://theexcelclub.com/looking-up-and-combining-text-using-textjoin-and-array-if-statements/#:~:te....
Good luck

[#3]

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.

[#4 · Accepted 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

[#5]

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