# 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.

## 1 Answer

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