How to write a multiple If(And formula for my specific purpose - Please Help!

[bldoolit]

I have a fairly simple task I'm trying to do, but I can't seem to figure out how to do it properly. I'm stuck!

I have a sheet with which I'm trying to create a retail price of a product depending on the cost of that product. This number changes depending on data I input. I want to be able to divide my cost by a tiered scale which is driven by the cost amount.

The sum of my cost is cell B7. Based on the value of B7, I want to be able to automatically have the proper formula applied to divided B7 by .2, .25, .3, or .35.

If B7 were to equal $51, I'd want to divide B7 by .25. However, If it were $49, I'd want to divide by .2. And if B7 were $501, I'd divide it by .35.

Here's my tiers with the amount I want to divide B7 by:

If B7 is less than $50.00, divide by 0.20
If B7 is more than $50, but less than $100.00, divide by 0.25
If B7 is more than $100, but less than $200.00, divide by 0.30
If B7 is more than $200, but less than $500, divide by 0.33
If B7 is more than $500, divide by .35

How should I be doing this?

[Whizbang]

Use a lookup function.

http://www.techonthenet.com/excel/formulas/lookup.php
http://www.contextures.com/xlfunctions02.html

=$B$7/VLOOKUP($B$7,Sheet2!$A:$B,2,True)

[zbor]

It's not problem, but you missing one criteria:

from 0 to 50 is 0,2
50 - 100 is 0,25
100 - 200 is 0,3
200 - 500 is 0,35

But from your example I understand that under 500 you want 0,3 and above 500 0,35.. So which is correct?

For above formula is:

=B7/LOOKUP(B7,{0,50,100,200},{0.2,0.25,0.3,0.35})

But for 500 add one more criteria

= B7/LOOKUP(B7,{0,50,100,200,500},{0.2,0.25,0.3,0.35,0.4})

[bldoolit]

This is EXACTLY what I was looking for! Thank you so much. I'd worked with vlookups before and didn't think it'd get me there, but the straight lookup I hadn't used. You're right about my data, it was a bit skewed and didn't fully encapsulate what I needed. Thanks again!