0
=ARRAYFORMULA(SUMPRODUCT((Products!B2:B=D2)*(IFS(H2="x",Products!D2:D,I2="x",Products!E2:E,J2="x",Products!F2:F)*(E2:E))))

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 x

What functions or arrays should I be using differently.

0
=IFERROR(PRODUCT(IFS(H2="x",VLOOKUP(D2,Products!$B$2:$I$100,3,0),I2="x",VLOOKUP(D2,Products!$B$2:$I$100,4,0),J2="x",VLOOKUP(D2,Products!$B$2:$I$100,5,0)),E2),"")

was able to achieve this with a little assistance from a friend

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.