Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value with items in different rows
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value with items in different rows
=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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value with items in different rows
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value with items in different rows
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value with items in different rows
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value with items in different rows
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup duplicate items in a list | Excel Worksheet Functions | |||
Lookup match 2 items | New Users to Excel | |||
Using the lookup Function for similar items to add together | Excel Worksheet Functions | |||
Lookup items and have picture appear together. | Excel Worksheet Functions | |||
Sum items in a lookup table. | Excel Discussion (Misc queries) |