2
=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.

2 Answers 2

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

0

In your solution you are repeating the VLOOKUP unnecessarily:

=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), "")

Since it is only the VLOOKUP's index that varies, you can include a single VLOOKUP and use the IFS function for that argument:

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

Other Code Reductions

  • The PRODUCT function can be replaced with the multiplication sign *
  • Sheets doesn't require specifying an empty string "" in IFERROR.
    eg. IFERROR(value) is acceptable, no need for IFERROR(value, "")
  • If IFERROR is being used to handle VLOOKUP errors when no "x" is found, it can be replaced by IFNA as those errors will be #N/A.
  • You could use the LOOKUP function rather than IFS which will avoid repeating the search string and allow you to use the range H2:J2 rather than one-off cells.
=IFNA(VLOOKUP(
   D2, Products!$B$2:$I$100,
   LOOKUP("x", H2:J2, {3,4,5}))*E2)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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