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
Check out the Formula Handbook template!