#### London2008

##### New Member
Hey there

I have never posted before, but always read the forum and the help is amazing, but i'm i have one question myself, which i can't find the answer to.

I need to work out a formula that provides the amount of pension costs we have to pay. ie.

If salary is between 0 and £19,500 the rate is 16.7%
If salary is between £19,501 and £40,500 the rate is 18.8%
If salary is between £40,501 and £69,000 the rate is 21.8%
If salary is between £69,001 and above the rate is 24.3%

I can't for the life of me work out a formula that will look at the salary field and calculate the above rates and output the annual amount.

Has anyone go any ideas?

Mike

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### VoG

##### Legend
Hi and welcome to MrExcel.

Try

=LOOKUP(A1,{0,19501,40501,69001},{0.167,0.188,0.218,0.243})

#### MrKowz

##### Well-known Member
Give this a shot.

The formula I used in B2 is:
=VLOOKUP(B1,A4:B7,2)
Book2
ABCD
1Salary:\$15,000
2Rate:
16.7%
3
4\$016.7%
5\$19,50118.8%
6\$40,50121.8%
7\$61,00124.3%
Sheet1

#### Oaktree

##### MrExcel MVP
Welcome to the board.

So we're clear, if a salary is £20,000 should the calculation return £20,000 * 18.8% = £3,760? Or £19,500 * 16.7% + £500 * 18.8% = £3,350.5?

#### London2008

##### New Member
Wow, thank you so much. it has worked.

i can believe how easy it was! my brain was almost fried today.

thanks again