IF cell CONTAINS one/two/three different values, return corresponding values
Hello community,
I am attempting to return single or multiple values depending on a multiple dropdown column.
My dropdown column contains criteria "Consolidation", "Reduction", "Termination", "New"
Currently the formula works for single values with the following formula
=IF([Type of Project]@row = "Consolidation", "🝢", IF([Type of Project]@row = "Reduction", "︾", IF([Type of Project]@row = "New", "○", IF([Type of Project]@row = "Termination", "⨷"))))
I would like my formula to be able to return two/three/four of the values. For example if the "[Type of Project] cell had multiple criteria e.g. "Consolidation Reduction" I want my formula to return "🝢 ︾"
Many thanks in advance.
Best Answer

Hi @Sam Swain,
You should be able to use this formula to accomplish this:
=IF(HAS([Type of Project]@row, "Consolidation"), "🝢", "") + IF(HAS([Type of Project]@row, "Reduction"), "︾", "") + IF(HAS([Type of Project]@row, "New"), "○", "") + IF(HAS([Type of Project]@row, "Termination"), "⨷", "")
Example output:
Hope this helps  if there are any issues etc. then just post! ☺️
Answers

Hi @Sam Swain,
You should be able to use this formula to accomplish this:
=IF(HAS([Type of Project]@row, "Consolidation"), "🝢", "") + IF(HAS([Type of Project]@row, "Reduction"), "︾", "") + IF(HAS([Type of Project]@row, "New"), "○", "") + IF(HAS([Type of Project]@row, "Termination"), "⨷", "")
Example output:
Hope this helps  if there are any issues etc. then just post! ☺️

Awesome, that worked perfectly! Thanks Nick!

No problem; happy to have helped! 😃

Hello!
I have a similar situation however I would like a formula that counts the amount of selections made in a cell and then return a value based on the number of selections.
For example, IF cell contains 4 or more multi select values, then return value.
Thanks,

You can use COUNTM to count how many values are selected in a cell  see: COUNTM Function
Then based on that value, you can instruct your formula what to do using IF statements. For example:
=IF(COUNTM([Column Name]@row) >= 4, "Return Value")
Cheers,
Genevieve

Thank you so much Genevieve that worked perfectly!
Best,
Help Article Resources
Categories
Check out the Formula Handbook template!