VLookup, returning a value on a vector

[ben.fish]

Hi, I want to use the V lookup function to return a value, but rather than returning the value in the same row as the match in the specified column, I want to return the value in the cell below.

Eg.

=VLOOKUP(B397, A$267:H$351, 7, FALSE)

... but rather than returning the value in column 7 on the row that matched with B397, I want the field below.

Any ideas how I do this?

Thanks.

[darkyam]

You can do it with an Index/Match combo, such as

=INDEX(A$267:H$351,MATCH(B397,A$267:H$351,0)+1,7)

[ben.fish]

Doesnt seem to work.

If i replace this

=(VLOOKUP(B397, A$267:H$351, 7, FALSE))

with

=INDEX(A$267:H$351,MATCH(B397,A$267:H$351,0)+1,7)

I get N/A returned.

Any ideas?

[darkyam]

Does B397 match A351? I don't use this formula very often, but I don't think it will work if it is trying to return a value outside the range. If this is not the case, can you please post the workbook so I can play with the formula until it works? It worked fine for me on my small test array.

[ben.fish]

B397 matches with A267, and what I want to return is G268 (ie 7 columns along from the match and 1 row down).

Thanks for your help so far.

[darkyam]

Can you please post the workbook so I can play with it until I get it right? As I said, it worked when I tried it on a test array, so I don't think I'll be able to solve it without seeing what's really going on. If anyone else reading this has any ideas, feel free to chime in.