Giving letters a number value

I would like to change my work schedule so that I use a legend/key instead of entering numbers for the hours.

For example instead of entering 8 hours for an employee I would like to use the letter 'A' to signify not only the 8 hours but the 'A' is also representative of the building the employee should be at. The bldg info is not included within the employee schedule, it's really just a reference. I've created a table where different letters are equal to a certain number (ie A=8hrs, B=8hrs,...g=12hrs-different bldg's require different hours). Using vlookup I've tried to create a total at the end of each row that will calculate what each value stands for and add it together at the end in a total column. Here is the formula I have but when I use it is not giving me the result I need. Is this even possible, to have a legend whose values correspond to a number and then to create a total column based on the values from the legend? Any help would be greatly appreciated


=VLOOKUP("a,b,c,d,e,f,g,h",G43:H50,2)

=SUMPRODUCT(COUNTIF($G$43:$H$50,{"a","b","c","d","e","f","g","h"})*{1,2,3,4,5,6,7,8})

I thought since I would like each letter to represent a specific value of either 8, 8.5 or 12 that vlookup should work. Can you tell me why it didn't work?

VLOOKUP takes only one value and is looking for data not to summarize them.
Test my formula and see if it works

That worked perfectly. Thank you so much. I was getting so frustrated with the vlookup.

Originally Posted by Tlyke212

That worked perfectly. Thank you so much. I was getting so frustrated with the vlookup.

You are welcome.
If you have not that this alredy can you click the star under my name,please?