Formula to calculate price based on sheet2

[aharvestofhealth]

Ok, I'm trying to modify the formula below and I'm not sure if it's even possible (although knowing you guys, I'm sure it is ). I've been using the following formula for a while and it works great, but I need to add to it if possible.

=IF(ISNUMBER(MATCH(A2,'Price Overrides'!$A$2:$A$400,0)),ROUND(CHOOSE(MATCH(VLOOKUP(A2,'Price Overrides'!$A$2:$C$400,3,0),{"Add";"Subtract";"Divide By";"Multiply By";"Exactly"},0),C2+VLOOKUP(A2,'Price Overrides'!$A$2:$D$400,4,0),C2-VLOOKUP(A2,'Price Overrides'!$A$2:$D$400,4,0),C2/VLOOKUP(A2,'Price Overrides'!$A$2:$D$400,4,0),C2*VLOOKUP(A2,'Price Overrides'!$A$2:$D$400,4,0),VLOOKUP(A2,'Price Overrides'!$A$2:$D$400,4,0))*10+0.01,0)/10-0.01,C2)

Currently the formula looks to the 'Price Overrides' sheet to calculate if it should Add, Subtract, Divide By or Multiply By the retail price. I would like to take this one step further and have the formula also calculate based on my choice of the Retail or Cost. In other words, to have the formula for example add $2 onto the Retail, or multiply by 2 on the Cost.

An example of what I'm trying to achieve can be seen from the spreadsheet below:

\1


On the first tab (Scan Genius Import), Column 'D' is where the formula is being calculated. Column 'C' is my retail, and Column 'B' is my cost.

One the second tab (Price Overrides), Column 'D' is my denominator, Column 'C' decides the equation (+,-,x, etc), and Column 'B' is the one I want to have added to my existing formula, so I can choose either the Retail or the Cost.

Also attached below is the working spreadsheet!

[NBVC]

Please download spreadsheets directly into the forum.... Don't use outside sources.

Edit your post.. click Go Advanced, click the paperclip icon and then Browse and Upload the file.

[aharvestofhealth]

Ok, thanks! Didn't know I could do that!

[NBVC]

Getting ugly... but try:

Please Login or Register  to view this content.

[aharvestofhealth]

Perfect! Works exactly how I wanted! I don't know how you do it...anyway Happy New Year!