I have a spreadsheet with an Orders sheet and a Products sheet. Product names (from B2:B) are data validated on the Orders sheet (D2:D) and also have adjacent cells with the quantity (E2:E) and 3 columns indicating what discount was given by use of an "x" (Consultant [H2:H], Preferred [I2:I], Retail [J2:J]). There are up to 3 prices per product (on Products sheet) reflective of the discount columns on the Orders sheet. I am trying to do the following:
Match ordered product
(Orders!Product(D2:D)) with product name
(Products!Product(B2:B)) multiply quantity
(Orders!Qty(E2:E)) by price
(Products!'Consultant Price'(D2:D), Products!'Preferred Price'(E2:E), Products!'Retail Price'(F2:F)) if discount
(Orders!Consultant(H2:H), Orders!Preferred(I2:I), Orders!Retail(J2:J)) equals
What functions or arrays should I be using differently.