how to get the value in the upper 5 cell
 

Dear all,

the purpose is to do a subtract to two cells, which are in the same colume
but one is upper 5 to the other.

First I have a vlookup string, "=VLOOKUP(B2,sheet2!A:M,3,FALSE)". Suppose
that the result of this vlookup value is located in sheet2!C10, I want to get
other value which is sheet2!C5. Cause C10 is not stable, it is located by
this vlookup string, how can I get the value of C5?

P.S. the value of each colume is not continous.

If anyone can give some hints, many many thanks to you!

how to get the value in the upper 5 cell
 

One way:

=INDEX(Sheet2!A:M,MATCH(B2,Sheet2!A:A,0)-5,3)

Note that if the lookup_value is in the range Sheet2!A1:A5 you'll get an
incorrect result since there aren't 5 cells above. I'm assuming your design
and request have taken that into account.

Biff

"ViestaWu" wrote in message
...
Dear all,

the purpose is to do a subtract to two cells, which are in the same colume
but one is upper 5 to the other.

First I have a vlookup string, "=VLOOKUP(B2,sheet2!A:M,3,FALSE)". Suppose
that the result of this vlookup value is located in sheet2!C10, I want to
get
other value which is sheet2!C5. Cause C10 is not stable, it is located by
this vlookup string, how can I get the value of C5?

P.S. the value of each colume is not continous.

If anyone can give some hints, many many thanks to you!