I have a table containing data of the National Insurance contributions that the employer must make on behalf of its employees. See table below. The contribution that must be made is based on the salary of the employee. For example, if the salary is 10,000 per month the contribution payable is different to that for an employee with salary of 1,000 per month.
I need to be able to enter a salary in a cell, then a formula takes the salary, look between the lower limit and upper limit of earnings to determine which Earning class is appropriate. Once the correct class is located, the appropriate employer's contribution is returned.
I am using the vlookup function, but by itself it does not yield the results. I would appreciate some help in suggesting a function that could used in conjunction with the vlookup. I would be happy to for help. Thanks
<table style="border-collapse: collapse; width: 344pt;" width="459" border="0" cellpadding="0" cellspacing="0"><col style="width: 61pt;" width="81"> <col style="width: 83pt;" span="2" width="111"> <col style="width: 117pt;" width="156"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 61pt;" width="81" height="20"> </td> <td colspan="2" class="xl70" style="border-left: medium none; width: 166pt;" width="222">Range of earnings</td> <td class="xl67" style="border-left: medium none; width: 117pt;" width="156"> </td> </tr> <tr style="height: 45pt;" height="60"> <td class="xl69" style="border-top: medium none; height: 45pt; width: 61pt;" width="81" height="60">Earnings Class</td> <td class="xl69" style="border-top: medium none inherit; border-left: medium none inherit; width: 83pt;" bordercolor="#808080" width="111">Lower level Monthly Earnings</td> <td class="xl69" style="border-top: medium none; border-left: medium none; width: 83pt;" width="111">Upper level Monthly Earnings</td> <td class="xl69" style="border-top: medium none inherit; border-left: medium none inherit; width: 117pt;" bordercolor="#808080" width="156">Employer's Weekly Contribution</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" height="20"> I</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">433</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">692.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">8.58</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20"> II</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">693</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">952.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">12.54</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20">III</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">953</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1256.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">16.84</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20">IV</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">1257</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1559.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">21.46</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20">V</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">1560</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1906.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">26.4</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20"> VI</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">1907</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2296.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">32.02</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20"> VII</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">2297</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2686.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">37.96</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20"> VIII</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">2687</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3076.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">43.9</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20">IX</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">3077</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3509.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">50.16</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20">X</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">3510</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3942.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">56.76</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20">XI</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">3943</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">4376.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">63.36</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit; border-bottom-color: inherit;" bordercolor="#808080" height="20"> XII</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit; border-bottom-color: inherit;" bordercolor="#808080">4377</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">4810.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit; border-bottom-color: inherit;" bordercolor="#808080">66.66</td> </tr> </tbody></table>
I need to be able to enter a salary in a cell, then a formula takes the salary, look between the lower limit and upper limit of earnings to determine which Earning class is appropriate. Once the correct class is located, the appropriate employer's contribution is returned.
I am using the vlookup function, but by itself it does not yield the results. I would appreciate some help in suggesting a function that could used in conjunction with the vlookup. I would be happy to for help. Thanks
<table style="border-collapse: collapse; width: 344pt;" width="459" border="0" cellpadding="0" cellspacing="0"><col style="width: 61pt;" width="81"> <col style="width: 83pt;" span="2" width="111"> <col style="width: 117pt;" width="156"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 61pt;" width="81" height="20"> </td> <td colspan="2" class="xl70" style="border-left: medium none; width: 166pt;" width="222">Range of earnings</td> <td class="xl67" style="border-left: medium none; width: 117pt;" width="156"> </td> </tr> <tr style="height: 45pt;" height="60"> <td class="xl69" style="border-top: medium none; height: 45pt; width: 61pt;" width="81" height="60">Earnings Class</td> <td class="xl69" style="border-top: medium none inherit; border-left: medium none inherit; width: 83pt;" bordercolor="#808080" width="111">Lower level Monthly Earnings</td> <td class="xl69" style="border-top: medium none; border-left: medium none; width: 83pt;" width="111">Upper level Monthly Earnings</td> <td class="xl69" style="border-top: medium none inherit; border-left: medium none inherit; width: 117pt;" bordercolor="#808080" width="156">Employer's Weekly Contribution</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" height="20"> I</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">433</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">692.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">8.58</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20"> II</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">693</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">952.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">12.54</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20">III</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">953</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1256.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">16.84</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20">IV</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">1257</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1559.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">21.46</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20">V</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">1560</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">1906.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">26.4</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20"> VI</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">1907</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2296.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">32.02</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20"> VII</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">2297</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2686.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">37.96</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20"> VIII</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">2687</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3076.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">43.9</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20">IX</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">3077</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3509.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">50.16</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit;" bordercolor="#808080" height="20">X</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">3510</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">3942.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit;" bordercolor="#808080">56.76</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" bordercolor="#808080" height="20">XI</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">3943</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">4376.99</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" bordercolor="#808080">63.36</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none inherit; height: 15pt; border-left-color: inherit; border-bottom-color: inherit;" bordercolor="#808080" height="20"> XII</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit; border-bottom-color: inherit;" bordercolor="#808080">4377</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">4810.99</td> <td class="xl67" style="border-top: medium none inherit; border-left: medium none inherit; border-bottom-color: inherit;" bordercolor="#808080">66.66</td> </tr> </tbody></table>
