Help creating an formula to multiply weights to proportional scale to create an index score.

I have narrowed my use case to simplify the question but I will need to scale this once I can get it working.

Sheet Columns

For this example I have 8 columns. In one I have a dropdown of options listed in sequence as 1. 2. 3. 4. and 5 (lets call this column A). In another column I have a weight I've assigned (Lets call this column B). In 5 other columns, I have a proportional multiplier that would apply depending on the selected dropdown (lets call these columns v-z). I have another column to house this formula (lets call this column C)


A person will make a selection form the dropdown in column A. Column C would then take column B and multiply it by the appropriate proportional multiplier. If Option 1. is selected column C would multiply column B and column V. If 2. was selected, column C would multiply column B and column W and so on and so forth.


I have tried various if and contains to help C identify the selected option in A an multiply it by a column in V-Z. The formulas I've tried to no avail didn't look at other options as I was intentional about let me see if it can identify one option and simply multiplying it by a 3. If I can do that, then I'd try replacing that 3 with the proper column. If that worked I'd try scaling it to include the other moving parts but I can't even get past the first part. Help is needed Thank you.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!