Vlookup: returning the value below the actual row


S

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
 
Ad

Advertisements

G

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
 
B

Bondi

starguy said:
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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top