Can you use multiple vlookup's to generate one answer?

[Brian Prihode]

I need to look up 3 different matches to generate an answer, and I was unsure of how to do this?

As an example. I need it to match the piece of fruit, the state it was grown in, and the season, and then for it to return to me who the seller is.
In the spreadsheet there are multiple variations of these combinations.

Can this be done, what would the formula look like?

EggHeadCafe - Software Developer Portal of Choice
Exchange UserCredential between client and webservice in.NET
http://www.eggheadcafe.com/tutorial...f-e3a718f0d96f/exchange-usercredential-b.aspx

 

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

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

 

[herochenna]

Visit www.ExcelGoodies.Com