I have a data sheet with multiple values in multiple columns. I have a qty and a current price which when multiplied out gives me the current revenue (CurRev).

I want to use this lookup table to give me the new revenue (NewRev) from the new price but can't figure out how to do multiple ifs in a lookup.

What I want is to build a new column that checks the "Product", "Tier" and "Location/State" and gives me the new price from the lookup table (above) and then multiply that by the qty.

e.g. Data

> Product,  Tier,  Location, Qty, CurRev,   NewRev 
> Product1, Tier1, VIC,      2,   $1000.00, $6000 (2 x $3000) 
> Product2, Tier3, NSW,      1,   $100.00,  $200 (1 x $200)
> Product1, Tier3, SA,       5,   $250.00,  $750 (5 x $150)
> Product3, Tier1, ACT,      5,   $100.00,  $500(5 x $100)
> Product2, Tier3, QLD,      2,   $150.00,  $240 (2 x $240)

Worst case, if I just get the new rate I can create another column