Formula, I almost have it

[scott]

I have created a table as follows:
A B C
range Tax on amount in column
2 0 0 18%
3 $10,000 $1,800 20%
4 20,000 3,800 22%
5 40,000 8,200 24%
6 60,000 13,000 26%
7 80,000 18,200 28%
8 100,000 23,800 30%
9 150,000 38,800 32%
10 250,000 70,800 34%
11 500,000 155,800 37%
12 750,000 248,300 39%
13 1,000,000 345,800 41%
14 1,250,000 448,300 43%
15 1,500,000 555,800 45%
16 2,000,000 780,800 49%

The third column is the rate applied to the excess over
column A, which should be added to the column B number.

My formula is =LOOKUP(C5,A2:A16,B2:B16)which works good to
lookup the base tax amount. But how to add the applicable
% rate on the excess above column A? Thanks

 

[merjet]

The formula you cited as working did not work for me.
Regardless, you can try the following.
E1: 50000
E2: =VLOOKUP(E1,A$2:C$16,2)
E3: =VLOOKUP(E1,A$2:C$16,3)*(E1-VLOOKUP(E1,A$2:C$16,1))

You can, of course, combine the formulas into one.

HTH,
Merjet

 

[Scott]

Thanks. Combining the formulas into one is a trick I
haven't got the hang of yet. I have a few places where it
takes me 3 steps to finally arrive at the desired end
result.

 

[steve]

Scott,

Used to be that way, too. And sometimes when it gets complex enough, I
still am.

One trick is to use separate cell for the bits and pieces. Than in another
cell build the combo you want.

Like
E4: =E1+E3/E2
Than copy and paste the formulas from the designated cells over there
reference in the formula.
so
E4: =
50000+VLOOKUP(E1,A$2:C$16,2)/VLOOKUP(E1,A$2:C$16,3)*(E1-VLOOKUP(E1,A$2:C$16,
1))

(just an example).