0

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.

enter image description here

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

5
  • Maybe I miss something, but where do you have the field qty on the source table? Where is Location/State? Oct 10, 2012 at 6:41
  • Is the Product column in the lookup table filled with merged cells, or is that just border formatting?
    – Excellll
    Oct 10, 2012 at 13:25
  • Also, are the Tier labels shown here just as they are in the real data (i.e. are they named "Tier 1", "Tier 2", etc.)? And are there exactly 3 Tiers for each Product?
    – Excellll
    Oct 10, 2012 at 13:28
  • @adam There is no qty in source table. Location is the header row. Oct 10, 2012 at 21:38
  • @Excellll the product column in merged. AND Yes, tier labels are the same in the real data Oct 10, 2012 at 21:38

2 Answers 2

0

This solution makes two big assumptions about your data: 1) that each product has exactly three tiers; and 2) the product IDs in the lookup table are in merged cells. Please let me know if this is not the case.

Here is a formula that will give you the NewRev value for a record in row 18.

=VLOOKUP(B18,INDIRECT("B"&MATCH(A18,$A$1:$A$10)&":J"&MATCH(A18,$A$1:$A$10)+2),MATCH(C18,$A$1:$J$1,0)-1,FALSE)*D18

This formula uses the INDIRECT function to define which part of the lookup table to use for your VLOOKUP based on the Product ID. The column number for the VLOOKUP is attained by using a MATCH function to find the appropriate location.

0

I think it would be easier if column A showed the relevant product on every row. If you do that you can use this formula

=INDEX(C$2:J$10,MATCH(1,INDEX((A$2:A$10=A18)*(B$2:B$10=B18),0),0),MATCH(C18,C$1:J$1,0))*D18

....which follows a standard INDEX/MATCH/MATCH setup to get data from the intersection of a column and row header (complicated slightly here by having to match two criteria to get the row)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .