Forum Discussion
Trying to use AND formula and TEXTJOIN IF - Not Working
I am trying to return multiple values from a table based on two different conditions. In my current formula below it is giving me the return of C5 (month) and C6 (sport), but I want it to return the ...
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
hi hans - i attached an example. So what i'd like is it to only return college football in Q3, and if no sport is selected to return everything that is just in Q3.
Try
=TEXTJOIN(" , ",TRUE,IF(IF(C3="",Sports!B2:B37<>"",Sports!B2:B37=C3)*IF(C4="",Sports!C2:C37<>"",Sports!C2:C37=C4),Sports!A2:A37,""))
See the attached version.
- Thanks Hans. If I dont select a sport it looks like it doesnt return anything. Im hoping for it to return any sport in Q3 if the "-" is selected. Any thoughts there?
Change the formula to
=TEXTJOIN(" , ",TRUE,IF(IF(C3="-",Sports!B2:B37<>"",Sports!B2:B37=C3)*IF(C4="-",Sports!C2:C37<>"",Sports!C2:C37=C4),Sports!A2:A37,""))
or perhaps better
=TEXTJOIN(" , ",TRUE,IF(IF(OR(C3={"","-"}),Sports!B2:B37<>"",Sports!B2:B37=C3)*IF(OR(C4={"","-"}),Sports!C2:C37<>"",Sports!C2:C37=C4),Sports!A2:A37,""))