Vlookup that retrieves data from a different row

[malehot888]

Hello,

This problem that i have should be an easy one. I'm trying to retrieve some data from a different row. I know index or offset should be used, but i am having difficulty putting it all together. any help would be appreciated.

 

[Samnuni]

You have made a request for help, now what is the problem?

 

[malehot888]

Darn, i knew i should've previewed my post.

So to be more clear, I am trying to vlookup a certain value. The value is located one row above the reference row. I hope this helps.

 

[Samnuni]

Vlookup(Lookup value, Table to lookup with value for return, column where return value is, the way you want to compare)

i.e. =vlookup(A1, B1:C15, 2, FALSE)

The value I am comparing is in cell A1, the table is from B1 to C15, the value that I want to return is in the second column of the table, I chose false because I want an exact match.

Note: There's a function called Hlookup that will do the same thing as Vlookup, but instead of searching by rows, it looks at columns. the way it works is the same.

 

[lenze]

If I understand you, this may work

=OFFSET(VLOOKUP(RefCel,Table,Col,FALSE),-1,0)
This should return the value one row up from the VLOOKUP value.

lenze

 

[malehot888]

Lenze,

The formula that you gave me is what I am looking for. But I tried inserting it in and it is giving me an error.

Here is my formula:
=offset(VLOOKUP(A2,'King Matt'!$A$2:$N$265,5,FALSE),-1,0)

Is there something I am doing wrong?

 

[Pekkavee]

Try

=INDEX(B2:B15,MATCH(C15,A2:A15,0)-1)


this is looking a value in C15 and finding a match in A2:A15 and then giving a match value in B2:B15 minus one row for it.

You can use this also for finding a value to the left of your original column.

Pekka

 

[lenze]

You're right. I wasn't thinking. The VLOOKUP returns a Value, not a location. I've done this before, but I don't remember what I used. I'll think about it.

lenze

 

[malehot888]

Figured it out... thanks guys

=IF(ISNA(INDEX('King Box'!E:E,MATCH(A2,'King Box'!$A$1:$A$1000,0)-1,1)),INDEX('King Matt'!E:E,MATCH(A2,'King Matt'!$A$1:$A$1000,0)-1,1),INDEX('King Box'!E:E,MATCH(A2,'King Box'!$A$1:$A$1000,0)-1,1))