I'm using a Vlookup function in cell A1 to return a value from a table. In A2
I would like the next item down on the same table to appear. What function
do I use in A2.

Using Excel 2003

Details

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JeffK" wrote in message
...
I'm using a Vlookup function in cell A1 to return a value from a table. In
A2
I would like the next item down on the same table to appear. What
function
do I use in A2.

Using Excel 2003

=match(a1,sheet2!a:a,0)
will return the row number of the matching value in column A of sheet2 (for the
value in A1).

If your =vlookup() looked like:
=vlookup(a1,sheet2!a:b,2,false)
then you could replace that =vlookup() with a formula like:
=index(sheet2!b:b,match(a1,sheet2!a:a,0))
(but you don't need to--I only suggest it for consistency's sake.)

And to get the value of the cell under that matching row, you could use:
=index(sheet2!b:b,1+match(a1,sheet2!a:a,0))
(the =match() returns the row number of the match. The we add 1 to come down a
row.)



JeffK wrote:

I'm using a Vlookup function in cell A1 to return a value from a table. In A2
I would like the next item down on the same table to appear. What function
do I use in A2.

Using Excel 2003

--

Dave Peterson

Beautiful Dave, appreciate your help

"Dave Peterson" wrote:

=match(a1,sheet2!a:a,0)
will return the row number of the matching value in column A of sheet2 (for the
value in A1).

If your =vlookup() looked like:
=vlookup(a1,sheet2!a:b,2,false)
then you could replace that =vlookup() with a formula like:
=index(sheet2!b:b,match(a1,sheet2!a:a,0))
(but you don't need to--I only suggest it for consistency's sake.)

And to get the value of the cell under that matching row, you could use:
=index(sheet2!b:b,1+match(a1,sheet2!a:a,0))
(the =match() returns the row number of the match. The we add 1 to come down a
row.)



JeffK wrote:

I'm using a Vlookup function in cell A1 to return a value from a table. In A2
I would like the next item down on the same table to appear. What function
do I use in A2.

Using Excel 2003

--

Dave Peterson