 # IF function with multiple choices in one cell

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

# Re: IF function with multiple choices in one cell

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.

# Re: IF function with multiple choices in one cell

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

# Re: IF function with multiple choices in one cell

Thank you. I'll check later

# Re: IF function with multiple choices in one cell

Thank you for the answers