Create numerical score based on answers chosen from a list

[wilsonmedic]

Greetings!

I am creating an Excel spreadsheet to help rate candidates for a program. We have a lot of interviews and we use a rubric based on answers. I want to create a score based on pre-selected answers.

What I have now is a list questions that list five answers each. The top answer is 5 points, second answer is 4 points, and so on:

5 points - Strong evidence that service is part of their identity (family tradition, current volunteering)
4 points - Some evidence that service is part of their identity
2 points - Interested in learning about service
1 point - Lacks history of service, does not seem a part of identity
0 points - No history of volunteerism, service is not part of their identity

When I choose one from a list, I want my final answer sheet to log the points, because they will be added up. For example:

Service Score: 2 points <------ This is where I am looking for a formula. A number that is created based on what was chosen off of a list.

And then additional questions will be added and calculated.

Thanks for the assist!

[etaf]

depending on how the spreadsheet is layed out - you could use a table to lookup the answer and return the value

can you load a sample spreadsheet here