VLookup, returning a value on a vector

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.

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

Please Login or Register  to view this content.

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?

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.

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.

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.

darkyam writes:

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

Correct me if I'm mistaken but I think in Excel 2000, the MATCH array needs to be a single row or column. Has this been improved in newer versions?

Your formula works for me if I put the VLOOKUP within the MATCH function and narrow the array to row G (column 7) like so;

Please Login or Register  to view this content.

ChemistB

Duh, NBVC's solution is much simplier. I didn't need to match the row in column 7 when we were already looking in column 1. Nevermind.

ChemistB

NBVC,
You're right. I looked back at the formula I had had in my test array and it was one column wide. I made the mistake when I tried to adjust the ranges to fit the OP's request. My mistake.