IF Function for multiple values

Aatish Chaudhary

Can someone please help me with the formula where if the column1 has any value from the list (i.e. test1, test2, test3, test4) the output should be "Test Result".

I tried this with the formula =IF([Column1]@row, = "Test1" + "Test2" + "Test3" + Test4","Test Result") but the output is coming #INVALID DATA TYPE

Andrée Starå

@Aatish Chaudhary

Excellent!

You're more than welcome!

Yes, it will work with 50 different column values.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mark Cronk

Hello,

Try:

=IF(CONTAINS( Column1@row, JOIN(COLLECT(test:test))=1, "Test Result","")

Mark

Aatish Chaudhary

Hello Mark, No it didn't work. #UNPARSEABLE

I tried this too =IF(CONTAINS( Column1@row, JOIN(COLLECT(test:test))=1, "Test Result",""))

Thanks for your help.

Andrée Starå

Hi @Aatish Chaudhary

I hope you're well and safe!

Try something like this. (if your column name has a number, it has to be surrounded by square brackets)

=IF(CONTAINS([Column1]@row, JOIN(COLLECT(test:test))=1, "Test Result","")

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Aatish Chaudhary

Thanks Andrée but this formula is still not working. My column name is "Type" and it doesn't have a number so we can go without [ ]. And the Test1,Test2,Test3,Test4 actual values are "Data", "Card", "Support" and "Expense".

Andrée Starå

@Aatish Chaudhary

Happy to help!

Can you share the exact formula you have in the cell?

Aatish Chaudhary

Sure, Here it is. =IF(CONTAINS(Type@row, JOIN(COLLECT(Data,Card,Support,Expense))=1, "Test Result","")

Andrée Starå

@Aatish Chaudhary

Try something like this instead.

=IF(OR(CONTAINS("Data", Type@row), CONTAINS("Card", Type@row), CONTAINS("Support", Type@row), CONTAINS("Expense", Type@row)), "Test result", "")

Did that work?

Aatish Chaudhary

Sorry but still not working and getting "#UNPARSEABLE"

Just fyi.. We are taking the reference within the sheet and not from the other sheet. I tried the below formula and its working fine but since we have 3 more values so not sure how to extend the formula to include other values too.

=IF([Type]@row = "Data","Test Result","")

Andrée Starå

@Aatish Chaudhary

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Aatish Chaudhary

I created another sheet to share with you then I realized that my column name had a two words "Task Type" so after renaming my column with just one word this formula is working.

Can I still use this formula to extend if I have 50 different column values?

=IF(OR(CONTAINS("Data", Type@row), CONTAINS("Card", Type@row), CONTAINS("Support", Type@row), CONTAINS("Expense", Type@row)), "Test result", "")

Mark Cronk

Thank you @Andrée Starå . Brilliant. Mark

Andrée Starå

@Aatish Chaudhary

Excellent!

You're more than welcome!

Yes, it will work with 50 different column values.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Mark Cronk

Happy to help!

Thanks for the kind words!

Aatish Chaudhary

Hello Andrée,

I truly appreciate your help!

I got another challenge due to multiple different output text within the same formula. I have all the values in the same column "Type" but the output should be different based on the values as mentioned below. Could you please help one more time how I can extend the formula to meet the below conditions?

For Example:

Output: "Test result" for column values Data, Card, Support, Expense

Output: "Final result" for column values General, Item, Test

Output: "No result" for column values Content, Text, Regular

Aatish Chaudhary

I tried this formula and it seems working fine though please confirm it this is a right way to do this

=IF(OR(CONTAINS("Data", Type@row), CONTAINS("Card", Type@row), CONTAINS("Support", Type@row), CONTAINS("Expense", Type@row)), "Test result", IF(OR(CONTAINS("General", Type@row), CONTAINS("Item", Type@row), CONTAINS("Test", Type@row)), "Final result","")