Forum Discussion
TEXTJOIN AND not working
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],""))
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
4 Replies
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. 🙂
- Hi there,
I think the following link can be useful:
https://theexcelclub.com/looking-up-and-combining-text-using-textjoin-and-array-if-statements/#:~:text=TEXTJOIN%20lets%20you%20select%20both,select%20the%20text%20we%20want.&text=This%20test%20will%20look%20and,it%20will%20return%20a%20blank.
Good luck- 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.
