Help on XLOOKUP formula with multiple criteria

I need help to formulate a formula with multiple criteria.

On the ORDER tab, I want to get the UNIT PRICE column based from the QTY column, UNIT column and CATEGORY column.
It will need to lookup on the PRODUCT tab which shows the different amount per PRICE CATEGORY. Each product have a different quantity per package.

BOX = has different quantity
PAIL and BAG = 1 quantity only

Btw, I define the following names:

TBL_PRODUCT =PRODUCT!$B$3:$F$7
PRICE_CATEGORY =PRODUCT!$D$3:$F$3
QTY_PER_PACKAGE =PRODUCT!$C$3:$C$7

Hi
What;s the difference between box/pail/piece?

If you check the PRODUCT tab, you'll see a quantity package

Each product indicates the quantity of each package which is in pieces.

PAIL and BAG is equivalent to 1 Piece
BOX, depends on the quantity of each package

e.g. Apple contains 5pieces per BOX
Mango contains 3pieces per BOX
Strawberry contains 10 pieces per BOX
Orange juice is equivalent to 1 PAIL
Rice is equivalent to 1 BAG

Thanks for replying.

Ah yeah.. I put the wrong amount on the unit price of the Strawberry. This answers my question however is it possible to make it Dynamic?

Like what if I added a new product on the table without modifying the formula.

Hi,
For DYNAMIC formula -
please see attached in column H:
=INDEX(PRODUCT!$D$4:$F$8,MATCH(B5:B10,PRODUCT!B4:B8,0),MATCH(E5:E10,PRICE_CATEGORY,0))*IF(D5:D10="PIECE",1,VLOOKUP(B5:B10,PRODUCT!B4:C8,2,0))

Worksheet name : ORDER

Cell F5 formula , Drag down

HTML Code: