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

I’ve a spreadsheet with an Orders sheet and a Merchandise sheet. Product names (from B2:B) are information validated on the Orders sheet (D2:D) and still have adjoining cells with the amount (E2:E) and three columns indicating what low cost was given by use of an “x” (Advisor [H2:H], Most popular [I2:I], Retail [J2:J]). There are as much as three costs per product (on Merchandise sheet) reflective of the low cost columns on the Orders sheet. I’m attempting to do the next:

Match ordered product (Orders!Product(D2:D)) with product title (Merchandise!Product(B2:B)) multiply amount (Orders!Qty(E2:E)) by worth (Merchandise!'Advisor Value'(D2:D), Merchandise!'Most popular Value'(E2:E), Merchandise!'Retail Value'(F2:F)) if low cost (Orders!Advisor(H2:H), Orders!Most popular(I2:I), Orders!Retail(J2:J)) equals x

What features or arrays ought to I be utilizing otherwise.