Forum Discussion

jkollie's avatar
jkollie
Copper Contributor
Jul 12, 2024

Excel VLOOKUP with Multiple Criteria (Dynamic Price Tiers) to determine Markup Price

I have a table with four columns: Price Tier, Min, Max, % rate. I want to use a vlookup in excel to determine the appropriate % rate based on the input from the user, and then compute the markup price (dynamic price tiers).

 

I have tried these formulae with no success:

 

1. To get the price tiers, I used: LOOKUP(2,1/((H3>=H6:H17)*(H3<=I6:I17)),G6:G17)

2. To get the % rate, I used: =LOOKUP(I3,tiers,customers)

 

I would appreciate any help.

 

Also, how do I use mins and maxes in excel? I don't mean minimum and maximum. It puzzles me that I can't even get a hint about it in excel.

 

Again, I would appreciate your help in solving this problem.

  • jkollie 

     

    Hi,

     

    Why are you using the function LOOKUP() ?

    Could you please guide us which are the conditions/logic behind the calculation of the price tiers and the % rate? What is the expected result?

     

    I believe it would be much easier to help you if you could attach a small sample document without sensitive data or at least share a screenshot of sample data.

    • jkollie's avatar
      jkollie
      Copper Contributor
      Hi, Agosto. Thanks for your interest to help. I did finally figure out the solution.

      Thanks again.
  • jkollie 

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Resources