Offsetting the vlookup function
 

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,

Offsetting the vlookup function
 

=OFFSET(INDIRECT("A"&MATCH("apple",$A$1:$A$9,0)),1 ,1,1,1)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"clubin" wrote:

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,

Offsetting the vlookup function
 

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)



clubin wrote:

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,

--

Dave Peterson

Offsetting the vlookup function
 

Hi,

try this

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

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

Mike

"clubin" wrote:

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,

Offsetting the vlookup function
 

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)

"clubin" wrote:

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,