Excel - Nested IF formula for ranging values?

Question

i just want to know how to do this without VBAs such that when i input a value within the ranges in column a, the corresponding column b values will appear. if there are other efficient methods, that would be much appreciated.

column a column b

1000-1249.99 80.70

1250-1749.99 116.00

1750-2249.99 151.30

2250-2749.99 186.70

2750-3249.99 222.00

3250-3749.99 257.30

3750-4249.99 292.70

4250-4749.99 328.00

4750-5249.99 363.30

5250-5749.99 398.70

5750-6249.99 434.00

6250-6749.99 469.30

6750-7249.99 504.70

7250-7749.99 540.00

7750-8249.99 575.30

8250-8749.99 610.70

8750-9249.99 646.00

9250-9749.99 681.30

9750-10249.99 716.70

10250-10749.99 752.00

10750-11249.99 787.30

11250-11749.99 822.70

11750-12249.99 858.00

12250-12749.99 893.30

12750-13249.99 928.70

13250-13749.99 964.00

13750-14249.99 999.30

14250-14749.99 1034.70

14750-above 1090.00

Answer 1

Sorry, but now your column A is text.  If you'd used two columns, one for the low and one for the high, this would be easy.  Now you're looking at programming.

Answer 2

ok, first you're going to have to split the low & high ranges into separate columns.

so starting in A1 put 1000

in B1 put 1249.99

in C1 put 80.70

do this all the way down for all your ranges (A2, B2, C2, etc)

now although you could build all this into one huge freakin' if statement, it would be very cumbersome and really wouldn't gain much.  

instead...

let assume your "input" cell is in E1

in D1 put

=IF(AND($E$1>=A1, $E$1<=B1), C1, 0)

copy this formula all the way down

now whatever you type in E1 (say 9751), you'll see all the rows evaluate to 0 EXCEPT the row that has your answer (681.30).

now in E2 enter a =SUM(D1:D29)

your answer will now appear in E2.

and if you like you can now hide the column with all the IF stmts (col D)

there you go...no VBA :-))))