Multiplying 2 columns where one column has letters representing numbers

[catteeya]

This is probably me being dim, but anyway....

I have a spreadsheet of project issues that includes 2 columns rating their current status (likelihood and impact). One column uses values shown as numbers (1-5) and the other uses letters A-E. The letters have numeric values 1-5. These columns are manually multiplied and entred in a third column to give an overall status rating.

I can't change the format of the spreadsheet, (otherwise I'd just make both columns numbers!) so I want to put a formula into the third column that will multiply the first two automatically, (without me having to think about which number "C" stands for!). I've looked all over but so far all the examples I've tried just come up with errors (#NAME, and #VALUE most often) or Excel tells me I've created a circular reference - so now I am just getting frustrated!!

Any help really, really, REALLY, appreciated!!

[bebo021999]

A column: numbers list
B column: Assuming A,B,C,D,E represents 1,2,3,4,5
C1=A1*MATCH(B1,{"A","B","C","D","E"},0)

[catteeya]

Thanks, but I'm still getting #VALUE error message. The numbers column is F, and the letters column is G. (And for reasons I'm not privy to, they are in reverse order - ie A=5, B=4 etc) And the third column is I - but I did transpose the cell references in the above and that still didn't work.....

[bebo021999]

So try to convert to:
I1=F1*MATCH(G1,{"E","D","C","B","A"},0)
Does it work?

[Xx7]

Try this...

[catteeya]

Nope bebo021999 tried that, but still didn't work - but I probably did it wrong, thanks anyway.
Thanks Xx7 - that's perfect!!