Lookup cell 1 below another cell
 

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

Lookup cell 1 below another cell
 

=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!)

Nate wrote:

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

--

Dave Peterson

Lookup cell 1 below another cell
 

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)

--
Biff
Microsoft Excel MVP


"Nate" wrote in message
...
I'm trying to add a formula that will return the cell directly below the
cell
that my VLOOKUP formula pulled from. Any suggestions?

Lookup cell 1 below another cell
 

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.

"Dave Peterson" wrote:

=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!)

Nate wrote:

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

--

Dave Peterson

Lookup cell 1 below another cell
 

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

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Nate" wrote in message
...
I'm trying to add a formula that will return the cell directly below the
cell
that my VLOOKUP formula pulled from. Any suggestions?

Lookup cell 1 below another cell
 

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

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)

--
Biff
Microsoft Excel MVP


"Nate" wrote in message
...
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.

"Dave Peterson" wrote:

=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!)

Nate wrote:

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

--

Dave Peterson

Lookup cell 1 below another cell
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Nate" wrote in message
...
Sorry. I wasn't familiar with the match formula at first. This is
exactly
what I was looking for. Thanks!

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Nate" wrote in message
...
I'm trying to add a formula that will return the cell directly below
the
cell
that my VLOOKUP formula pulled from. Any suggestions?