Conditional Lookup? Area Discount Table

[MonkeyBoyATL]

I have a spreadsheet which is doing what I need it to with Static, pre-determined values, but I would like to create a formula which will look at a table of percentages and depending on what the user input is, the formula will look at what range that value falls between, will assign a certain percentage discount. Here is the an example of what I am doing with everything assigned with pre-determined values...

Progressive Area Pricing Calc2.xls
	A	B	C	D	E	F	G	H	I	J	K
2
3			Parameters						UserInput
4		BasePrice(1Sq.Ft)	$40.00					Qty	15
5								Height	6
6		AreaDiscountTable			QtyDisc.Table			Width	6
7	SqFt	Disc.%	Price/SqFt.		Qty	Discount
8	1	0.00%	$40.00		1	0%		SqFt.	0.25
9	2	37.50%	$25.00		2	5%
10	8	73.00%	$10.80		5	15%		UnitPrice	$10.00	w/oQtyDiscount
11	24	81.00%	$7.60		10	25%
12			$-		15	30%		QtyDiscount	30%
13			$-		20	35%		UnitPrice	$7.00	w/QtyDiscount
14			$-
15			$-					Subtotal	$105.00
16			$-
Sheet2

What I want the new formula to do is look at the user input and then from the Area Discount Table, determine that if the value is between 2 and 7.99 that it should return 37.5%, between 8 and 23.99 then return 73%, 24 and higher then return 81%.

Does this make sense? Basically the user needs to have control of the parameters and set up the tables as they see fit depending on the situation- then they plug in the sizes and qty and test the pricing calculations. Is there a way to use VLOOKUP, MATCH or OFFSET to determine if a value falls within a range, then return a value based on that logic?

Seems like this might be similar to a Tax Table...
Thanks in advance!

 

[Brian from Maui]

From your example, enter in I12,

=VLOOKUP(I4,$E$8:$F$13,2)

 

[just_jon]

Do you want the minimum of 2 qualifying discounts, or the max? [Hey, BfM!]

 

[Yogi Anand]

Hi MonkeyBoyATL:

Let us have a look at this ...

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3									UserInput
4		BasePrice(1sft)	$40.00					Qty	15
5								Height	6
6		AreaDisc.Table			QtyDisc.Table			Width	6
7	sft	Disc.%	Pricepsf		Qty	Discount
8	0	0			1	0		sft	0.25
9	1	0	$40.00		2	5%
10	2	37.50%	$25.00		5	15%		UnitPrice	$10.00	w/oqtyDisc.
11	8	73%	$10.80		10	25%
12	24	81%	$7.60		15	30%		QtyDisc	30%
13					20	35%		DiscPrice	$7.00	withqtyDisc.
14
15								Subtotal	$105.00
16
Sheet3

Is this what you are looking for? I have used VLOOKUP formula with use of the size and quantity discounting. Please post back if you need to discuss this further.