Lookup cell 1 below another cell

N

Nate

I'm trying to add a formula that will return the cell directly below the cell
that my VLOOKUP formula pulled from. Any suggestions?
 
D

Dave Peterson

=match(a1,sheet2!a:a,0)
will give you the row where A1 matches the value in sheet2 column A.

=match(a1,sheet2!a:a,0)+1
will give you the row under the match.

So
=index(sheet2!a:a,match(a1,sheet2!a:a,0)+1)
should return the value under that match.

(as long as there is a match and it's not in the last row!)
 
T

T. Valko

If your VLOOKUP formula was something like this:

=VLOOKPU(A1,A2:B100,2,0)

Then try something like this:

=INDEX(B2:B100,MATCH(A1,A2:A100,0)+1)
 
N

Nate

For some reason everytime I try the match formula it returns the #N/A error.
My vlookup is VLOOKUP(A2, Mfg!C2:L22695, 3, FALSE). When I write the match
formula I'm using MATCH(B2, Mfg!C2:L22695, 0) but that alone returns an
error. B2 is the cell that contains the vlookup. I've tried the match
formula just using the cell that was my lookup value for the vlookup, but
even that returns an error. Every lookup value I'm using has data in the
table array. Any idea what I'm doing wrong? Thanks for all your input.
 
N

Nate

Sorry. I wasn't familiar with the match formula at first. This is exactly
what I was looking for. Thanks!
 
T

T. Valko

My vlookup is VLOOKUP(A2, Mfg!C2:L22695, 3, FALSE).
When I write the match formula I'm using MATCH(B2,Mfg!C2:L22695, 0)
but that alone returns an error
Click to expand...

The MATCH lookup_array must be a 1 dimensional array. That is, a single row
or single column.

=VLOOKUP(A2, Mfg!C2:L22695, 3, FALSE)

=INDEX(Mfg!E2:E22695,MATCH(A2,Mfg!C2:C22695,0)+1)
 
Top