Lookup formula returning wrong answer

[mattwolves]

Hi folks,

I've got a spreadsheet with column 'A' having either N/A, Low, Medium or High.
In column 'B' I've got the following formula =lookup("range",{"High","Medium","Low","N/A"},{"10","5","1","0"})
I want column B to provide one of the following responses depending on what is in the cell on column A
High=10
Medium =5
Low=1
N/A =0

No matter which way around I do the formula I either have Low resulting with 10 or High resulting with #N/A.
I have also tried VLOOKUP("range",{"N/A","0";"Low","1";"Medium","5";"High","10"},2,0) but I have similar issues with returning the result.

I'm sure its something pretty simple but completely missing the trick.

Hope I've explained myself well enough for someone to help.

Thanking you in advance

 

[Scott T]

Is this what you want?

Excel 2010
	A	B
2	low	1
3	medium	5
4	high	10
5	N/A	0
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,{"high","Low","Medium","N/A"},{10,1,5,0})
B3	=LOOKUP(A3,{"high","Low","Medium","N/A"},{10,1,5,0})
B4	=LOOKUP(A4,{"high","Low","Medium","N/A"},{10,1,5,0})
B5	=LOOKUP(A5,{"high","Low","Medium","N/A"},{10,1,5,0})

 

[mattwolves]

Perfect. Thank you.

 

[Scott T]

You are welcome.