Vlookup: returning the value below the actual row

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
>
>

Use
activecell.offset(1).value

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

thank you all.