# Google Sheets SUMPRODUCT and IFS on different sheets

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

``````=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

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)
``````