Challenging Formula Help

[ziggy18]

Hi Mr.Excel.com Forum!

I've learned so much from this forum but now I've got a question that I cannot seem to figure out.

I am creating a spreadsheet that is serving as an invoice. There are two products (out of 20) that if the quantity of either one OR a combination of both add up to 3 or more, the price changes.

For example - see image here for the Excel layout: https://covenantwines.com/wp-content/uploads/ExcelQuestion.jpg

I am using 3 wines as an example:
Wine 1 = $168
Wine 2 = $168
Wine 3 = $250

If the quantity of Wine 1 is 3 or more
OR
the quantity of Wine 2 is 3 or more
OR
the quantity of Wine 1 and Wine 2 is 3 or more (e.g. they order 2 of wine 1 and 3 of wine 2)
THEN
the price of Wine 1 AND Wine 2 in the price column changes to $144
The price of wine 3 should never change and is generated in the invoice by a vlookup, but can always appear on the invoice.

Is there a formula that would check to see if Wine 1 and/or Wine 2 appear in the invoice, and then add the quantity to see if it is more than 3 and, if so, change the price to $144 from $168.

Right now I have a line-by-line formula that works. The problem is that it does not check about the combo if both Wine 1 AND Wine 2 are in the invoice.
=IF(OR(AND(C20=Data!$B$3, A20>=3), AND(C20=Data!$B$4, A20>=3)),144,IFERROR(VLOOKUP(C20,Data!$B$3:$C$100,2,FALSE),""))
B3 is Wine1
B4 is Wine 2
and if false, that means that it's a different product and it pulls the price of the product via a VLOOKUP.

Thanks everybody!

 

[ziggy18]

SORRY....one last thing. In the link above, the price SHOULD change on Wine 1 and Wine 2 to $144 each because there are a total of 3 products. Thanks.