How to match 2 criteria ?


Hi, im trying to get some help to have a formula to have a summary of a questionnaire, based on a scoring sheet. So the formula will need to match 2 criteria:

1st criteria is to match the question number

2nd criteria is to match the response to the options to yield a target.

i tried index/collect but i realise my 2nd criteria is from a table, hence its not working. Any suggestions ?

Best Answer

  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 11/16/22 Answer ✓

    so something like this should work based on whether they choose low medium or high.

    =if(response@row = "Low", index(low:low,match(questionnumber@row, [question number]:[question number], 0)),

    if(response@row = "Medium", index(medium:medium ,match(questionnumber@row, [question number]:[question number], 0)),

    if(response@row = "High", index(high:high ,match(questionnumber@row, [question number]:[question number], 0))



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!