Offsetting the vlookup function


C

clubin

I am trying to use the vlookup function to find data in one colum and then
return a value in a different row. I tried using a combination of vlookup and
offset to no avail and I could use some help.

To illustrate I have data set up like this:

A B
1 apple 17
2 15
3 16
4 pear 2
5 10
6 9


I want to use a formula that will lookup a given item in column "A" and
return the associated value in column "B" offset by one or more rows. Meaning
I would like a formula that would return "15" for apple and "10" for pear,
rather than the straightforward vlookup that would return "17" and "2"
respectively.

Any help would be much appreciated.

Thank you,
 
Ad

Advertisements

D

Dave Peterson

If you have Apple in A1 of Sheet1 and your table is in Sheet2, you could use:

=match(a1,sheet2!a:a,0)
to get the row number for the first match

So
=match(a1,sheet2!a:a,0)+1
will give the row number for the 15 (one row after the first match).

Then you can use that as an index into column B of sheet2:

=index(sheet2!b:b,match(a1,sheet2!a:a,0)+1)
 
M

Mike H

Hi,

try this

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

In practice id use a cell ref for apple/pear etc

Mike
 
Ad

Advertisements

B

bpeltzer

I'd use index and match... =index(B:B,match("apple",a:a,0)+xxx) where xxx is
the number of rows by which you want to offset the vlookup result. (Match
works much like vlookup except that it returns the item index of the match
instead of the value of a corresponding entry)
 

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