Lookup Based on Min/Max 1

[briangriffin]

This would seem to be a common task, but the formula isn't working for me.

Price list looks like this:

 Row     A      B       C         D         E
        Min    Max    Tier 1    Tier2     Tier3
 4      1       10     1.50      2.50      3.50
 5      11      20     1.25      2.25      3.25
 6      21      30     1.00      2.00      3.00

I need to enter a value in a cell titled Quantity and have it return the corresponding row.

Here is what I have:

=IF(AND($A$16>=$A4,$A$16<=$B4),C4,IF(AND($A16>$A5,$A16<=$B5),C5,0))

where A16 is the quantity cell, and row 4 corresponds to the row with the 1 - 10 quantities.

It works fine when I enter a quantity less than 10, but anything > 10 returns 0, rather than the lower price.

Another problem is that I will probably have more than 7 price tiers, so the If statement won't really work. I'm wondering if there is a better way to do this? For now, if I can get 7 tiers working that would get me moving forward.

Thanks.

 

[Gruuuu]

=INDEX($A:$E,MATCH(your_quantity,$A:$A,1),MATCH(your_tier,$3:$3,1))

Expand the Indexed columns ($A:$E in this example) to contain all your tier info, and you can have as many tiers as you like

 

[Gruuuu]

To point out a couple things:
You'll notice that it's only targeting the MIN column. This is correct.
The last parameter in the MATCH() formula is match_type. 0 is explicit and requires an exact match, 1 will seek the next lowest value.

 

[briangriffin]

Thanks, this will do the trick, but I'm not clear on one thing - what is the 'your_tier' value?

The first Match is working, but can't quite get the second one to cooperate.

 

[briangriffin]

Got it, I was missing the tier input value.

I had to hardcode some ranges while I was trying to understand what was going on, and I left them in. This array won't grow by more than a couple of rows/columns, and I can change the formulas then if needed.

Thanks!