# IF Function for multiple values

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

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

Hello,

Try:

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

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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

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

• ✭✭✭✭✭✭
edited 06/19/21

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• 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".

• ✭✭✭✭✭✭

Happy to help!

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

• ✭✭✭✭✭✭

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

Did that work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

• ✭✭✭✭✭✭
edited 06/19/21

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)

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

Happy to help!

Thanks for the kind words!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Hello Andrée,

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

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!