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 vz). I have another column to house this formula (lets call this column C)
Workflow
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.
Issue
I have tried various if and contains to help C identify the selected option in A an multiply it by a column in VZ. 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.
Answers

Are you able to provide some screenshots with mock data manually entered for context?

@Paul Newcome does this help?

Assuming your dropdown selections in the [Certainty of Schedule] column will always start with the appropriate number followed by a period, you would use something along the lines of the below (updating column names to match what you have in your sheet.
=[Certainty of Schedule Weight]@row * INDEX([1st Weight Column]@row:[Last Weight Column]@row, VALUE(LEFT([Certainty of Schedule]@row, FIND(".", [Certainty of Schedule]@row)  1)))

I attempted this solution and cannot get it to work. Through different iterations, I keep getting either #UNPARSEABLE or #INVALID DATA TYPE.

Can you show your most recent attempt open in the sheet as if you are about to edit it?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!