Offsetting the vlookup function

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

 

[John C]

=OFFSET(INDIRECT("A"&MATCH("apple",$A$1:$A$9,0)),1,1,1,1)

 

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

 

[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

 

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