transformed vloopup

O

oercim

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.
 
C

Claus Busch

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
 
O

oercim

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top