CODEWITHSUNDEEP
excelif-statementexcel-formulamatchexcel-2007
Jason
Jason

Reputation: 13

Multiply values based on numerous criteria

I have two sheets: http://s4.postimg.org/aryzvl13v/Capture.png < image of the two tables.

I have to find the sell price in the first table. In order to do this I need to match the type. After that, I check if the cost is more than column B in the second table. Then I'd find the multiplier for the corresponding cost. Once I find the right multiplier, then I can find the Sell Price which equals the Cost x Multiplier.

So I need a formula for Column D that matches the type, then finds the biggest possible "If Cost is more than", and finally multiplies the corresponding multiplier with the cost.

For example: let's say I have item 467874 of type: PKG and a cost of $8.45. Because it's more than $3.00 but less than $10.00, I would use the multiplier 1.85.

So the Sell Price for 467874 would be 8.45 x 1.85 = $15.63

I need to be able to change Sheet2 later, so the formula would have to be dynamic. Otherwise I would just use "IF" statements. So I want to be able to add and delete fields onto Sheet2.

Upvotes: 1

Views: 147

Answers (1)

pnuts
pnuts

Reputation: 59485

With a changed lookup array in Sheet2:

SO30652011 example

I suggest: 

=ROUND(C2*IF(B2="PKG",INDEX(Sheet2!C$2:C$7,MATCH(C2,Sheet2!B$2:B$7,1)),IF(B2="POPULAR",INDEX(Sheet2!C$8:C$11,MATCH(C2,Sheet2!B$8:B$11,1)),INDEX(Sheet2!C$12:C$14,MATCH(C2,Sheet2!B$12:B$14,1)))),2)

in D2 copied down to suit.

Upvotes: 3

Related Questions