2 variable function in a formula
I have 2 variables that determine what the returning value is. First variable would be Name (row), second variable would be Score (column header). The Score value in the formula is a variable and is determined by data entry. For instance, if the [email protected] value is 99 it needs to find the column 99, if the [email protected] value is 72 it needs to find the column 72 and bring back the appropriate letter grade for each instance for that [email protected] (Name).
I've tried to do INDEX MATCH and IF(CONTAINS), but can't get the column range reference to be a variable as determined by the value found in the field [email protected]
Score 99 98 97 96 72
Name A A A A C
I have multiple schools with different grading scales and need to find the letter grade based on the school name and have the letter grade value returned for the course score. My primary goal is to have one transfer credit processing sheet that will reference the grading scale sheet and bring back the right letter grade for data upload.
Previously, I was using one Excel file per school to create the upload data with a grading scale maintained on a tab. I was using Excel VLookUP functions to bring back the value, but would like to integrate it onto Smartsheet as one sheet with all schools listed (easier to reference and to maintain).
Any ideas on how to get this to return the right information? Do I need to make separate sheets for each school (similar to how I was in Excel) or can I combine it like I'm trying to do? Is there a better way to set this up?