Need a formula that can sum the point value of a given list of categories for a person

[Sneaky323]

Hello Excel Guru's,

I need a formula that can fulfill this requirement:
1. Read name in sheet 2
2. Identify the name on multiple rows in sheet 1.
3. Read and Identify each category(6 categories total) that the individual is connected to in sheet 1, and sum the point value of the combined categories from a table in sheet 2.

I have tried a variety of IF, IF(AND, SUMIF, SUMIFS, VLOOKUP, INDEX(MATCH; with no avail.
Sheet 2 is where I have an array defined as "Category" which has the point value associated with each category.


***Another way of me getting this idea across*** So in Sheet2 Cell E2 I need a formula to read the name in sheet2 Cell C2, Go into Sheet 1 and examine the entire Sheet1!C:C. When it finds a match it needs to then go directly accross on Sheet1 to Column A and read the category but instead of reading the category, it takes the point value assigned to the category and stores it to be summed up after the formula has identified all matching criteria. If done correctly the answer in Sheet2! E2 for "Barb Throckmorton" would be 18.

Here is the excel sample attachment with all data regarding the above.

Thanks for the help!