I want to paste in a list of stock symbols and then use my own rating
system to rank them. I have a list of criteria I will use to rank them
with but I want to map written values to numeric values and use those
to create a score for each stock.

Ill try to explain in a little more detail. Below is a very simplified
version of what I am trying to achieve.

Column A | ColumnB | ColumnC | ColumnD | Score |
SYMBOL | Trend | Close Price| Volume |

ColumnB/C/D all use drop down lists with friendly names to pick from.
For example, ColumnB would have the choices Up, Down and Sideways as
the three options in the dropdown list. Each choice needs to map to a
numeric value. Let's say Up has a value of 1, Down has a value of -1
and Sideways has a value of 0.

Similarly, the other columns would be setup in the same manner.

Finally I want the Score cell to tally the values of columns B,C and D
etc.

I can get dropdown lists to make my friendly choices but I cannot get
any further. I tried using VLOOKUP because all the research I could
find made it seem like that was the correct method, but my Excel
ineptness becomes apparent in trying to make it work.

I would much appreciate some help please.

Oh, and to further complicate this a little, I would also like to make
each list configurable without having to recode a bunch of stuff. My
current scoring strategy is still very infantile and I expect to be
adjusting it many times before I settle on a scoring system I feel is
accurate. For this reason I would like to have each list on its own
worksheet rather than multiple ones on a single worksheet. This would
make it more intuitive to update for me...