Value in Next Row

  • Thread starter Thread starter JeffK
  • Start date Start date
J

JeffK

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.)
 
Beautiful Dave, appreciate your help

Dave Peterson said:
=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.)
Click to expand...
 
Back
Top