Formula to Identify difference in dropdown columns without numbers
Hello!
I am working on a sheet that has three columns where two are drop downs (Current and Desired) with the following criteria:
Enthusiastic Support
Support
Neutral
Reluctant
Obstruction
Unknown
I have for the third column labeled
Gaps. In this column I am trying to find a formula that calculates the difference between Desired and Current (Desired  Current). The problem I am running into is for the formula to assign a value to the criteria so the result is a number. So from the criteria mentioned earlier the values would go 6,5,4,3,2,1,0 in the same order. I have tried using VLOOKUP and INDEX/MATCH formulas but kept getting INVALID Ref.
Best Answer

You have values listed from 6  0, but only include 5 options. I wasn't sure if you were missing and option, or if it should only go to 5. This will give you results from 05.
=IF(AND([Current]@row <> "", [Desired]@row <> ""), IF([Current]@row = "Enthusiastic Support", 5, IF([Current]@row = "Support", 4, IF([Current]@row = "Neutral", 3, IF([Current]@row = "Reluctant", 2, IF([Current]@row = "Obstruction", 1, IF([Current]@row = "Unknown", 0))))))  IF([Desired]@row = "Enthusiastic Support", 5, IF([Desired]@row = "Support", 4, IF([Desired]@row = "Neutral", 3, IF([Desired]@row = "Reluctant", 2, IF([Desired]@row = "Obstruction", 1, IF([Desired]@row = "Unknown", 0)))))))
Answers

You have values listed from 6  0, but only include 5 options. I wasn't sure if you were missing and option, or if it should only go to 5. This will give you results from 05.
=IF(AND([Current]@row <> "", [Desired]@row <> ""), IF([Current]@row = "Enthusiastic Support", 5, IF([Current]@row = "Support", 4, IF([Current]@row = "Neutral", 3, IF([Current]@row = "Reluctant", 2, IF([Current]@row = "Obstruction", 1, IF([Current]@row = "Unknown", 0))))))  IF([Desired]@row = "Enthusiastic Support", 5, IF([Desired]@row = "Support", 4, IF([Desired]@row = "Neutral", 3, IF([Desired]@row = "Reluctant", 2, IF([Desired]@row = "Obstruction", 1, IF([Desired]@row = "Unknown", 0)))))))

@Carson Penticuff thank you this worked!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!