Formula help

[robert]

I'm pulling out my hair. LOL I'm try to create a range. (I think)

If a number falls between two numbers it returns a dollar amount. Example:
The ranges are
LEFT RIGHT
250 250 = $40
500 500 = $100
1000 1000 = $200
2000 2000 = $400
3000 3000 = $600
4000 4000 = $800
5000 5000 = $1000

If the "LEFT" cell's figure is 2500 and the "RIGHT" cell's figure is 3500
the it shows the amount from the 2000 range as $400

I hope that this is clear. Thanks to whomever helps. Much appreciated!!
Robert

 

[Shane Devenshire]

Hi,

It looks like you are saying that you want to take the lower of the two
value and return the associated value? If not what do you want to do in the
following cases:
Left Rigth Result
5000 100 ?
250 4000 ?
2999 3500 ?

If you just care about the first number you could use VLOOKUP. If the
following table was entered in the D1:F10 and A1 contained the LEFT value:
0 0
250 40
500 100
1000 200

=VLOOKUP(A1,D1:F10,2,TRUE)

Here everything between 0 and <250 return 0, anything between 250 and <500
return 40 and so on.