IF function with multiple choices in one cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2388417%22%20slang%3D%22en-US%22%3EIF%20function%20with%20multiple%20choices%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388417%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20a%20question%3CBR%20%2F%3EI%20have%20some%20data%20from%20the%20question%20of%20%22what%20fruit%20do%20you%20like%3F%22%3CBR%20%2F%3ESome%20of%20the%20answers%20have%20several%20choices%20of%20fruit%3CBR%20%2F%3ESo%2C%20what%20IF%20formula%20can%20be%20used%20for%20multiple%20options%20in%20one%20cell%3F%3CBR%20%2F%3EI%20attach%20the%20excel%20file%3CBR%20%2F%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2388417%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-2388648%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20function%20with%20multiple%20choices%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388648%22%20slang%3D%22en-US%22%3Efor%20some%20reason%20I%20couldn't%20download%20your%20sample%20file%20but%20here%20is%20a%20formula%20that%20should%20work%3A%3CBR%20%2F%3E%3DTEXTJOIN(%22%3B%22%2CTRUE%2CIFERROR(MATCH(%22*%22%20%26amp%3B%20%24E%242%3A%24E%2410%20%26amp%3B%20%22*%22%2C%24B2%2C0)*%24F%242%3A%24F%2410%2C%22%22))%3CBR%20%2F%3Ewhere%20B2%20is%20the%20input%20and%20columns%20E%20and%20F%20are%20the%20index%20and%20lookup%20values%20respectively%3CBR%20%2F%3Eif%20you%20want%2Fneed%20non-numeric%20then%20just%20change%20the%20product%20to%20be%20an%20IF%20statement.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388797%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20function%20with%20multiple%20choices%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388797%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063987%22%20target%3D%22_blank%22%3E%40azkiyawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%22%3B%20%22%2C1%2CIF(COUNTIF(XLOOKUP(G3%2C%24A%243%3A%24A%248%2C%24B%243%3A%24B%248)%2C%22*%22%26amp%3B%24E%244%3A%24E%247%26amp%3B%22*%22)%2C%24D%244%3A%24D%247%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I have a question
I have some data from the question of "what fruit do you like?"
Some of the answers have several choices of fruit
So, what IF formula can be used for multiple options in one cell?
I attach the excel file
Thank you

4 Replies
for some reason I couldn't download your sample file but here is a formula that should work:
=TEXTJOIN(";",TRUE,IFERROR(MATCH("*" & $E$2:$E$10 & "*",$B2,0)*$F$2:$F$10,""))
where B2 is the input and columns E and F are the index and lookup values respectively
if you want/need non-numeric then just change the product to be an IF statement.

@azkiyawn 

As variant

with

=TEXTJOIN("; ",1,IF(COUNTIF(XLOOKUP(G3,$A$3:$A$8,$B$3:$B$8),"*"&$E$4:$E$7&"*"),$D$4:$D$7,""))

 

Thank you. I'll check later
Thank you for the answers