Problem with formula

[ukrad]

Hi All,
I hope one of you helpful people can come to my rescue .

I have a formula:
=LOOKUP(R3,KeyStageOnePoints)-LOOKUP(Q3,KeyStageOnePoints)

with the lookup table like this:

WA	5
W	3
1A	11
1B	9
1C	7
2A	17
2B	15
2C	13
3A	23
3B	21
3C	19

<tbody>
</tbody>

So...I need to calculate the difference between some levels
eg. WA to 1C should give the answer 2 but it is coming up as -12???

Many thanks!

 

[FormR]

Welcome to MrExcel.

Try:
=VLOOKUP(R3,KeyStageOnePoints,2,0)-VLOOKUP(Q3,KeyStageOnePoints,2,0)

 

[ukrad]

Aaah thank you soooo much for this! It works brilliantly. What does the 2,0 mean in the formula?

 

[FormR]

You're welcome.

The two means return the value from the second column of the lookup table.

The zero (or false) means find an exact match.

 

[ukrad]

Thank you this has really helped me. Thanks for the quick reply too.