Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
Maybe I miss something, but where do you have the field qty on the source table? Where is Location/State? –  Adam Ryczkowski Oct 10 '12 at 6:41
    
Is the Product column in the lookup table filled with merged cells, or is that just border formatting? –  Excellll Oct 10 '12 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 '12 at 13:28
    
@adam There is no qty in source table. Location is the header row. –  php-b-grader Oct 10 '12 at 21:38
    
@Excellll the product column in merged. AND Yes, tier labels are the same in the real data –  php-b-grader Oct 10 '12 at 21:38

2 Answers 2

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.

share|improve this answer

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.