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

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))
)))
Answers

@HuiXian So someone is responding low medium and high and you want to return the number associated?

Yap, thats right

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))
)))

Thank you so much Samuel! it worked 😍!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!