Problem with IF CONTAINS formula with multiple IF


I am trying to return a value of 1 through 26 based on the corresponding number in a column (Sprint column). It works when I just use one set, but not all of them. I realize this formula is huge so if there is a better way to do this, please feel free to suggest. The number convention of the column contains a value and dates like 22.01 1/11/-1/24, 22.02 1/25-2/7, etc so I can't use =, CONTAINS seems to be the best option.

=IF(CONTAINS(".01", Sprint@row), "1", IF(CONTAINS(".02", Sprint@row), "2", IF(CONTAINS(".03", Sprint@row), "3", IF(CONTAINS(".04", Sprint@row), "4", IF(CONTAINS(".05", Sprint@row), "5", IF(CONTAINS(".06", Sprint@row), "6", IF(CONTAINS(".07", Sprint@row), "7", IF(CONTAINS(".08", Sprint@row), "8", IF(CONTAINS(".09", Sprint@row), "9", IF(CONTAINS(".10", Sprint@row), "10", IF(CONTAINS(".11", Sprint@row), "11", IF(CONTAINS(".12", Sprint@row), "12", IF(CONTAINS(".13", Sprint@row), "13", IF(CONTAINS(".14", Sprint@row), "14", IF(CONTAINS(".15", Sprint@row), "15", IF(CONTAINS(".16", Sprint@row), "16", IF(CONTAINS(".17", Sprint@row), "17", IF(CONTAINS(".18", Sprint@row), "18”, IF(CONTAINS(".19", Sprint@row), "19", IF(CONTAINS(".20", Sprint@row), "20", IF(CONTAINS(".21", Sprint@row), "21", IF(CONTAINS(".22", Sprint@row), "22", IF(CONTAINS(".23", Sprint@row), "23", IF(CONTAINS(".24", Sprint@row), "24", IF(CONTAINS(".25", Sprint@row), "25", IF(CONTAINS(".26", Sprint@row), "26")))

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!