Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup - Multiple references
X | Y | Z
0 | 0 | Case 1 0 | 1 | Case 2 1 | 0 | Case 3 1 | 1 | Case 4 I would like to use a vlookup statement that will allow me to lookup the values in x and y and return the value of z. Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup - Multiple references
Sumproduct is probably what you are looking for.
-- JNW "Elceller in distress" wrote: X | Y | Z 0 | 0 | Case 1 0 | 1 | Case 2 1 | 0 | Case 3 1 | 1 | Case 4 I would like to use a vlookup statement that will allow me to lookup the values in x and y and return the value of z. Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup - Multiple references
I am sorry, I was not clear. I would like to type in a value for x and a
value for Y. I would like the program to determine z. If I type in 1 for x and 0 for y, it should return the text case3. Thanks for your help. "JNW" wrote: Sumproduct is probably what you are looking for. -- JNW "Elceller in distress" wrote: X | Y | Z 0 | 0 | Case 1 0 | 1 | Case 2 1 | 0 | Case 3 1 | 1 | Case 4 I would like to use a vlookup statement that will allow me to lookup the values in x and y and return the value of z. Thanks for your help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup - Multiple references
How many different values are possible for X and Y?
-- JNW "Elceller in distress" wrote: I am sorry, I was not clear. I would like to type in a value for x and a value for Y. I would like the program to determine z. If I type in 1 for x and 0 for y, it should return the text case3. Thanks for your help. "JNW" wrote: Sumproduct is probably what you are looking for. -- JNW "Elceller in distress" wrote: X | Y | Z 0 | 0 | Case 1 0 | 1 | Case 2 1 | 0 | Case 3 1 | 1 | Case 4 I would like to use a vlookup statement that will allow me to lookup the values in x and y and return the value of z. Thanks for your help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup - Multiple references
X Y Z
Apple Red Company1 Apple White Company2 Apple Blue Company3 Apple Green Company4 Apple Yellow Company5 pear Red Company1 pear White Company2 Pear Blue Company3 The actule table has 50 rows. I would like to type in a value for x and a value for y and have the macro look up the x value, find the corresponding y value and return the z value. user input: x= Apple Y= Green Output z = Company4 "JNW" wrote: How many different values are possible for X and Y? -- JNW "Elceller in distress" wrote: I am sorry, I was not clear. I would like to type in a value for x and a value for Y. I would like the program to determine z. If I type in 1 for x and 0 for y, it should return the text case3. Thanks for your help. "JNW" wrote: Sumproduct is probably what you are looking for. -- JNW "Elceller in distress" wrote: X | Y | Z 0 | 0 | Case 1 0 | 1 | Case 2 1 | 0 | Case 3 1 | 1 | Case 4 I would like to use a vlookup statement that will allow me to lookup the values in x and y and return the value of z. Thanks for your help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup - Multiple references
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Elceller in distress wrote: X Y Z Apple Red Company1 Apple White Company2 Apple Blue Company3 Apple Green Company4 Apple Yellow Company5 pear Red Company1 pear White Company2 Pear Blue Company3 The actule table has 50 rows. I would like to type in a value for x and a value for y and have the macro look up the x value, find the corresponding y value and return the z value. user input: x= Apple Y= Green Output z = Company4 "JNW" wrote: How many different values are possible for X and Y? -- JNW "Elceller in distress" wrote: I am sorry, I was not clear. I would like to type in a value for x and a value for Y. I would like the program to determine z. If I type in 1 for x and 0 for y, it should return the text case3. Thanks for your help. "JNW" wrote: Sumproduct is probably what you are looking for. -- JNW "Elceller in distress" wrote: X | Y | Z 0 | 0 | Case 1 0 | 1 | Case 2 1 | 0 | Case 3 1 | 1 | Case 4 I would like to use a vlookup statement that will allow me to lookup the values in x and y and return the value of z. Thanks for your help. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup and combine all references | Excel Discussion (Misc queries) | |||
Indirect references with VLOOKUP | New Users to Excel | |||
Transposing Multiple Cell References to Multiple Values (NOT total | Excel Discussion (Misc queries) | |||
Transposing Multiple Cell references as Multiple Values | Excel Discussion (Misc queries) | |||
how do i use the vlookup with two references ? | Excel Worksheet Functions |