Creating a value from a variable result
 

To start with I'll explain how my sheet is set up:

Cells W40:W45 contain values of 40 through to 45 These are levels gained by
an individual.
Cells X40:X45 contain the grade needed to access the next level.
Looking something like this:-
W X
40 40 41,171
41 41 45,529
42 42 50,399
43 43 55,649
44 44 61,512
45 45 67,983

D27 contains the current level (40 - 45)
E27 contains the current grade (in this case 42,224)

Here's my query.
I want a calculation in E85 that finds D27 from within (W40:W45), matches
the corresponding value from (X40:X45), then subtracts E27 from that
corresponding value.

For example, D27 is 41, E27 is 42,224. The corrresponding X value is 45,529.

Resulting in 1,305 being shown in E85.

I have tried combining Vlookup's index's and matches but always get a #ref
result.

Sorry it's so spread out but this is just a few lines taken from the whole
sheet. If you could help with this 1 cell I can then expand it to the rest.

Any help would be greatly appreciated.

Thanks in advance, Darren.

Creating a value from a variable result
 

Ok, it was staring me in the face:
=LOOKUP(D27,W:W,X:X)-E27.


"Darren" wrote:

To start with I'll explain how my sheet is set up:

Cells W40:W45 contain values of 40 through to 45 These are levels gained by
an individual.
Cells X40:X45 contain the grade needed to access the next level.
Looking something like this:-
W X
40 40 41,171
41 41 45,529
42 42 50,399
43 43 55,649
44 44 61,512
45 45 67,983

D27 contains the current level (40 - 45)
E27 contains the current grade (in this case 42,224)

Here's my query.
I want a calculation in E85 that finds D27 from within (W40:W45), matches
the corresponding value from (X40:X45), then subtracts E27 from that
corresponding value.

For example, D27 is 41, E27 is 42,224. The corrresponding X value is 45,529.

Resulting in 1,305 being shown in E85.

I have tried combining Vlookup's index's and matches but always get a #ref
result.

Sorry it's so spread out but this is just a few lines taken from the whole
sheet. If you could help with this 1 cell I can then expand it to the rest.

Any help would be greatly appreciated.

Thanks in advance, Darren.