Prev: Displaying entire row
Next: how can I create a dummy variable in Excel?
From: Darren on 19 Apr 2010 13:30
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.



From: Darren on 19 Apr 2010 13:43
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.
>
>
>
 | 
Pages: 1
Prev: Displaying entire row
Next: how can I create a dummy variable in Excel?