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

Copper Contributor

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.

3 Replies

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

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?

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

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.

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

Hi, Agosto. Thanks for your interest to help. I did finally figure out the solution.

Thanks again.