vlookup - Multiple references

[Guest]

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.

 

[Guest]

Sumproduct is probably what you are looking for.

 

[Guest]

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.

 

[Guest]

How many different values are possible for X and Y?

 

[Guest]

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

 

[Dave Peterson]

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