Formula to find the in-between number in a range

[Nathan Asius]

I have built a spreadsheet to calculate custom orders. On my dashboard I have a cell which I named "Cost". This cell is a sum of the cost of all products elsewhere on the sheet.
I need to calculate the markup based on what the cost total comes to in this named cell.
On another worksheet I have this small table which lists the range of amounts and the percentage of the markup related to the total. So, for example, you can see on in the attached range of data, if the total cost comes to $1350.00 then a 10% markup applies. The first figure I could use a formula with and Equal To/Less than operator. And the last Figure I could simply use an Equal To/Greater Than operator. What is stumping me is the formulas for the middle two figures, to get in-between the minimum and maximums.
I have attempted two different formulas to calculate the right markup. Neither of them worked. I attached the formulas I used. Is it due to the Excel version I'm using that caused me to miss something?

Seems like it should be easier to solve this.

 

[dreid1011]

Give this a try:

Book2
	AB	AC	AD	AE
1
2
3				1350
4
5	Min $ Amt	Max $ Amt	Markup
6	$ -	$ 250.00	20%	135
7	$ 251.00	$ 1,000.00	15%
8	$ 1,001.00	$ 5,000.00	10%
9	$ 5,001.00	$ 5,000,000.00	8%
Sheet1

Cell Formulas
Range	Formula
AE6	AE6	=$AE$3*LOOKUP($AE$3,$AB$6:$AB$9,$AD$6:$AD$9)

$AE$3 would be your "Cost" cell. And you will have to add your sheet name in the references if the formula is going to be on a different sheet from either the Cost cell or the lookup table.

 

[Fluff]

Try

=if(cost=median(cost,ab6:ac6),cost*ad6,"no")