Return a value given that a number is within a certain range

I'm looking for a little help on my scheduled withdrawals problem. I currently solve this with nested IF functions, but I'm getting more and more ranges and that leads to more and more nested IF functions. On top of this I'm running optimization so at a certain point it just explodes.

The problem:

Low        High        Amount to withdraw
600000     2000000     32000
300000     599999      20000
0          299999      8000

So a user would enter in current storage, lets say 325000 and the sheet would spit out 20000. If a user submitted 153555 the answer would spit out 8000

Like I said with just 3 ratchets using nested if clauses isn't impossible, but there can be 10 ratchets and nested if clauses aren't feasible. I'd like to solve this with Excel formulas and not a macro.

Solution 1:

Set up your bracket information in the following form¹:

Low       Amount to withdraw
0         8000
300000    20000
600000    32000
2000000   #N/A

somewhere in your workbook; let’s say E2:F5 (with headers in Row 1).  Then use the function

=VLOOKUP(value, E$2:F$5, 2)

to lookup and retrieve the second (i.e., Amount to withdraw) value in the last row in the table where the E value (lower end of the bracket) is ≤ the lookup value; for example:

A slightly more realistic formula would be

=IF(ISBLANK(A1), "", VLOOKUP(A1, E$2:F$5, 2))

which will display a blank result for a blank input (rather than treating it as 0).

¹ We don’t have the high ends of the ranges in the table because we don’t need to; 299999 and 599999 are redundant when we have 300000 and 600000.  We include 2000000 as the low end of an undocumented bracket ranging from 2000000 to ∞.