I have data in following format.

col A B
1 aa
....bb
2 cc
....dd
3 ee
....ff
I want to lookup value of say 2 in above data and return "dd" which is
below the row in which 2 resides.
what is the formula to do this and what if I want to return the value
of 3rd row below the row the Vlookup value found in?

regards


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=567782

On the assumption that the number in column A only occurs once, then this
will find the next row down. If you want other "offsets" then change the "+1"
to "+2" etc,

=INDEX(B1:B9,MATCH(2,A1:A9,0)+1)

HTH

"starguy" wrote:


I have data in following format.

col A B
1 aa
....bb
2 cc
....dd
3 ee
....ff
I want to lookup value of say 2 in above data and return "dd" which is
below the row in which 2 resides.
what is the formula to do this and what if I want to return the value
of 3rd row below the row the Vlookup value found in?

regards


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=567782

starguy wrote:
I have data in following format.

col A B
1 aa
...bb
2 cc
...dd
3 ee
...ff
I want to lookup value of say 2 in above data and return "dd" which is
below the row in which 2 resides.
what is the formula to do this and what if I want to return the value
of 3rd row below the row the Vlookup value found in?

regards


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=567782

Hi,

Maybe you can use a combination of MATCH and INDEX. Something along the
lines of:

=INDEX(B1:B6,MATCH(2,A1:A6)+1)

If you want the next row just type +2

Regards,
Bondi

Use
activecell.offset(1).value


--
RealRaven
------------------------------------------------------------------------
RealRaven's Profile: http://www.excelforum.com/member.php...o&userid=37058
View this thread: http://www.excelforum.com/showthread...hreadid=567782

thank you all.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=567782