Formula for a range of numbers equals a number in a cell on a different sheet

Question

I am looking to do a formula in Excel to say a cell has a number inputted for example between 500-999 and it picks up a cost from a cell on another sheet.

so far I can only get 2 ranges to work using:

=If(C2<=999,Sheet1!H3,IF(C2>=1000,Sheet1!H4))

But if I do:

=If(C2<=999,Sheet1!H3,IF(C2>=1000,Sheet1!H4,IF(C2>=2000,Sheet1!H5)))

It still takes the value for >=1000

Any help on this will be grately appreicated.

Answer 1

Change the places of the conditions for 1000 and 2000. Like this:

=If(C2<=999,Sheet1!H3,IF(C2>=2000,Sheet1!H5,IF(C2>=1000,Sheet1!H4)))

In general, F9 can do a wonderful job, when you are working with Excel formulas.