Hello, I need to use vlookup in different way. As it is known well, vlookup finds a common key and insert a described cell for the related match. However, I just want to insert one above cell for the related match.

For example, let the "column "A" consist just a character "c" in its first row(A1). And Let F1G5 is a table like,

a 5
b 12
c 23
d 43
e 45
Then I apply =VLOOKUP(A:A;F:G;2;0) function in the B1 cell. Naturally it returns 23. However, what I want a way to return one above cell, which 12. How can I do that. I will be very glad for the help. Thanks a lot.

Hi Oercim,

Am Mon, 8 Apr 2013 04:55:27 -0700 (PDT) schrieb oercim:

For example, let the "column "A" consist just a character "c" in its first row(A1). And Let F1G5 is a table like,

a 5
b 12
c 23
d 43
e 45
Then I apply =VLOOKUP(A:A;F:G;2;0) function in the B1 cell. Naturally it returns 23. However, what I want a way to return one above cell, which 12. How can I do that. I will be very glad for the help. Thanks a lot.

try:
=INDEX(G1:G5,MATCH("c",F1:F5,0)-1)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you very much Mr. Busch for giving your time. Your reply was very helpful for me. Thanks again.