I am having trouble putting together a function that will lookup a given
value but return an associated value in a different row. i have tried
combinations of vlookup and offset to no avail.

To illustrate here is an example.

My table is set up as follows

A B
1 apple 10
2 20
3 30
4 pear 5
5 7
6 9

I would like to be able to lookup "apple" but instead of returning "10" I
need to return "20." Similarly, if I looked up "pear" i would need to return
"7" not "5."

Any help would be much appreciated.

Thank you in advance.

=INDEX(B1:B6,MATCH("pear",A1:A6,0)+1)


"clubin" wrote:

I am having trouble putting together a function that will lookup a given
value but return an associated value in a different row. i have tried
combinations of vlookup and offset to no avail.

To illustrate here is an example.

My table is set up as follows

A B
1 apple 10
2 20
3 30
4 pear 5
5 7
6 9

I would like to be able to lookup "apple" but instead of returning "10" I
need to return "20." Similarly, if I looked up "pear" i would need to return
"7" not "5."

Any help would be much appreciated.

Thank you in advance.

Perfect, thank you!

"Teethless mama" wrote:

=INDEX(B1:B6,MATCH("pear",A1:A6,0)+1)


"clubin" wrote:

I am having trouble putting together a function that will lookup a given
value but return an associated value in a different row. i have tried
combinations of vlookup and offset to no avail.

To illustrate here is an example.

My table is set up as follows

A B
1 apple 10
2 20
3 30
4 pear 5
5 7
6 9

I would like to be able to lookup "apple" but instead of returning "10" I
need to return "20." Similarly, if I looked up "pear" i would need to return
"7" not "5."

Any help would be much appreciated.

Thank you in advance.

I think I prefer the rudeness of not responding at all to multi posting.
Please don't do it.

"clubin" wrote:

Perfect, thank you!

"Teethless mama" wrote:

=INDEX(B1:B6,MATCH("pear",A1:A6,0)+1)


"clubin" wrote:

I am having trouble putting together a function that will lookup a given
value but return an associated value in a different row. i have tried
combinations of vlookup and offset to no avail.

To illustrate here is an example.

My table is set up as follows

A B
1 apple 10
2 20
3 30
4 pear 5
5 7
6 9

I would like to be able to lookup "apple" but instead of returning "10" I
need to return "20." Similarly, if I looked up "pear" i would need to return
"7" not "5."

Any help would be much appreciated.

Thank you in advance.

But if you notice that the multipost, you may want to warn others--especially if
there are responses at the other post(s).



Mike H wrote:

I think I prefer the rudeness of not responding at all to multi posting.
Please don't do it.

"clubin" wrote:

Perfect, thank you!

"Teethless mama" wrote:

=INDEX(B1:B6,MATCH("pear",A1:A6,0)+1)


"clubin" wrote:

I am having trouble putting together a function that will lookup a given
value but return an associated value in a different row. i have tried
combinations of vlookup and offset to no avail.

To illustrate here is an example.

My table is set up as follows

A B
1 apple 10
2 20
3 30
4 pear 5
5 7
6 9

I would like to be able to lookup "apple" but instead of returning "10" I
need to return "20." Similarly, if I looked up "pear" i would need to return
"7" not "5."

Any help would be much appreciated.

Thank you in advance.


--

Dave Peterson

Check your other post.

clubin wrote:

I am having trouble putting together a function that will lookup a given
value but return an associated value in a different row. i have tried
combinations of vlookup and offset to no avail.

To illustrate here is an example.

My table is set up as follows

A B
1 apple 10
2 20
3 30
4 pear 5
5 7
6 9

I would like to be able to lookup "apple" but instead of returning "10" I
need to return "20." Similarly, if I looked up "pear" i would need to return
"7" not "5."

Any help would be much appreciated.

Thank you in advance.

--

Dave Peterson