Cost Formula using multiple criteria

Jennifer C

I have been beating my brain trying to make this formula work. Any help is appreciated. I have two sheets, the Sample Sheet and the Sample Pricing Sheet. I want to be able to fill in the customer with the pricing options and product(s), then have the product cost autofill under the correct column(s).

The products have a standard and an option pricing. A customer could be getting multiple products. If they get product 1, I would like for the correct pricing (standard or option) to appear in that column. If they get product 2 the same. Screenshots below to hopefully help. Thank you in advance from the currently braindead. :)

Lucas Rayala

@Jennifer C, with your simplified scenario, you can brute force this as follows:

=IF(AND([Pricing Option]@row = "Standard Pricing", CONTAINS("Product 1", Products@row)), INDEX(COLLECT({Standard Cost}, {Product}, "Product 1"),1),"")
+ IF(AND([Pricing Option]@row = "Standard Pricing", CONTAINS("Product 2", Products@row)), INDEX(COLLECT({Standard Cost}, {Product}, "Product 2"),1),"")
+ IF(AND([Pricing Option]@row = "Option 1 Pricing", CONTAINS("Product 1", Products@row)), INDEX(COLLECT({Option 1 Cost}, {Product}, "Product 1"),1),"")
+ IF(AND([Pricing Option]@row = "Option 1 Pricing", CONTAINS("Product 2", Products@row)), INDEX(COLLECT({Option 1 Cost}, {Product}, "Product 2"),1),"")

Each "if" statement looks for a product/pricing combo and collects the corresponding price. The results are added.

However, this only works with a few products. You can make this more generic by replacing the product name with a formula that grabs the first product name and returns the pricing, then another formula that grabs the second product name and returns the pricing, etc. Again, you would stack these formulas and add them together. This is a decent scenario if you have a lot of different products, of which the customer is only going to grab one or two.