VLOOKUP - MULTIPLE RETURNS

[Chantelle]

I am attempting to create a menu database for our restaurant. I want to
return the name of a supplier when I choose a specific menu item so that in
my cost column the correct price comes across. However sometimes we may
source the same item from two or more suppliers. I am not sure how to write a
formulat that states lookup this menu item and if the item chosen has two or
more suppliers then choose this supplier and provide the price per kilo/litre
etc (based on the chosen supplier). Does that makes sense? Any help would be
gratefully appreciated.

 

[Stingz]

Hi,

If I am not wrong your base data looks like below (the one which has
information about the price of menu items supplied to you by the supplier..
(on sheet "data")
A B C D
1 Tea Supplier1 $10
2 Coffee Supplier1 $20
3 Tea Supplier2 $15
4 Coffee Supplier2 $22

and on your Menu Database you require the price to populate if you chose a
menu item and a supplier name. (if thats the case ... see the below example )

A B C D E
1 Menu item Supplier Cost Quantity Total
2 Tea Supplier1 $10 1 $10
3 Tea Supplier2 $20 2 $40
4

The formula you need to use here would be
sumproduct((Condition1)*(Condition2)* Range that needs to be populated)

here on C2 the formula would be

=sumproduct((A2=Data!$A$1:$A$4)*(B2=Data!$B$1:$B$4)*Data!$C$1:$C$4)

remember that the reference ranges need to contain only individual columns
and same row counts...
the Above formula would populate a sum function for all matches.. so if you
have a duplicate entry for the same conditions then it would populate the sum
of matches.
also if you want the count of matches instead of using the range
"*Data!$C$1:$C$4"
just multiply by 1.. ie..
=sumproduct((A2=Data!$A$1:$A$4)*(B2=Data!$B$1:$B$4)*1)
to obtain the count of matches.

Hope this solves your trouble..

 

[Stingz]

Forgot to mention ... you can add more conditions to the same:
for summation (if unique entries .. acts like a vlookup but only on numeric
values)
=sumproduct((Condition1)*(Condition2)*(Condition3)*(Condition4)....* Range
that needs to be populated)
For Count
=sumproduct((Condition1)*(Condition2)*(Condition3)*(Condition4)....*1)

 

[Chantelle]

Thanks for your help, I will have a go to see how this formula works.
Chantelle

 

[Chantelle]

Stingz - I love you! That worked perfectly. Thank you so much for your help,
I will definitely be using the online forum again!