Vlookup: returning the value below the actual row

[starguy]

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 i
below the row in which 2 resides.
what is the formula to do this and what if I want to return the valu
of 3rd row below the row the Vlookup value found in?

regard

 

[Guest]

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

 

[Bondi]

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

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

 

[RealRaven]

Use
activecell.offset(1).value

 

[starguy]

thank you all