How to lookup a value in a table with multiple criteria
- by php-b-grader
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
