# Range figures that will count same value based on table range figures

#### ambz123

##### Board Regular
Hi I am trying to figure out how do i formula for a range to give the same amount.

E.g employee has a Wages amount of 1030.01 he should contribute \$133 and employer should contribute \$92.
Is there any formula that we can set to achieve?
Employee Wages Range Employee Monthly Contribution
 Employers Monthly Contribution
 1020.01 - 1040.01
 \$ 133.00
 \$ 92.00

Employee-Provident-Fund-Calculator.xlsx
ABCDEFGIJKLNO
1Rate of increase of Basic Pay:5%figures to follow
2Employer's Contribution:13%Employer's Contribution:13%
3Employee's Contribution:9%Employee's Contribution:9%
4Round downRound down
5Actual payEmployee Monthly Contributionrounddown Employer Monthly ContributionrounddownTotal ContributionWages Range Employee Monthly ContributionEmployee Monthly ContributionEmployers Monthly ContributionEmployers Monthly ContributionRate of Interest
6
7\$ 1,030.00\$ 135.26\$ 135.00\$ 92.62\$ 92.00\$ 227.00 1020.01 - 1040.01 \$ 132.92\$ 133.00\$ 92.43\$ 92.008.50%
8\$ 1,050.00\$ 137.89\$ 137.00\$ 94.42\$ 94.00\$ 231.00 1040.01 - 1060.01 \$ 135.53\$ 136.00\$ 94.24\$ 94.008.50%
9\$ 1,070.00\$ 140.51\$ 140.00\$ 96.22\$ 96.00\$ 236.00 1060.01 - 1080.01 \$ 138.14\$ 138.00\$ 96.06\$ 96.008.50%
10\$ 1,090.00\$ 143.14\$ 143.00\$ 98.02\$ 98.00\$ 241.00 1080.01 - 1100.01 \$ 140.75\$ 141.00\$ 97.87\$ 97.008.50%
11\$ 1,110.00\$ 145.77\$ 145.00\$ 99.82\$ 99.00\$ 244.00 1100.01 - 1120.01 \$ 143.36\$ 144.00\$ 99.69\$ 99.008.50%
12\$ 1,130.00\$ 148.40\$ 148.00\$ 101.62\$ 101.00\$ 249.00 1120.01 - 1140.01 \$ 145.96\$ 146.00\$ 101.50\$ 101.008.50%
Sheet3
Cell Formulas
RangeFormula
B7:B12B7=A7*\$C\$2*1-N7
C7:C12C7=ROUNDDOWN(B7,0)
D7:D12D7=A7*\$C\$3*1-N7
F7:F12F7=C7+E7
K7:K12K7=H7*\$J\$3*1-N7

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Joe4

If you create a lookup range that splits out the range into multiple columns, you can use VLOOKUP with the approximate match argument to do what you want.

Set up your VLOOKUP range like this:
Column 1: Lower range value
Column 2: Upper range value (optional)
Column 3: Employee Monthly Contribution
Column 4: Employer Monthly Contribution

And make sure that this range is sorted from low to high by column 1.
So, the first row in this lookup table would be:
Column 1: 1020.01
Column 2: 1040.00
Column 3: 133
Column 4: 92

If this lookup range were in the cells G7:J12, then the formula to return the Employee Contribution would be:
VBA Code:
``=VLOOKUP(A7,\$G\$7:\$J\$12,3,TRUE)``
and the formula to return the Employer Contribution would be:
VBA Code:
``=VLOOKUP(A7,\$G\$7:\$J\$12,4,TRUE)``

#### ambz123

##### Board Regular
Hi is there a way when i input the actual wages and it will auto lookup the employee monthly contribution and employer monthly contribution that falls in the range?
and the code do i use vba coding?

Employee-Provident-Fund-Calculator.xlsx
ABCDEFG
1Lower Range Higher Range Employee Monthly ContributionEmployer Monthly ContributionTo in Actual Wages here Look up values for Employee Look up values for Employer
21020.011040.0113694109014399
31040.011060.0113896
41060.011080.0114198
51080.011100.0114399
61100.011120.01146101
71120.011140.01149103
81140.011160.01151105
91160.011180.01154107
101180.011200.01156108
111200.011220.01159110
121220.011240.01162112
131240.011260.01164114
141260.011280.01167116
151280.011300.01169117
161300.011320.01172119
Sheet4
Cell Formulas
RangeFormula
F2F2=VLOOKUP(E2,\$A:\$D,3,TRUE)
G2G2=VLOOKUP(E2,\$A:\$D,4,TRUE)
A3:A16A3=B2
B3:B16B3=A3+20

#### Joe4

Sure. Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code into the VB Editor window that pops-up:
VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim lr As Long
Dim rng As Range

'   If more than one cell entered at a time, skip
If Target.CountLarge > 1 Then Exit Sub

'   See if entry is in column E after row 2
If Target.Column = 5 And Target.Row >= 2 Then
Application.EnableEvents = False
'       Find last row in column A with data
lr = Cells(Rows.Count, "A").End(xlUp).Row
'       Build lookup range
Set rng = Range("A2:D" & lr)
'       Lookup values and enter into columns F and G
Target.Offset(0, 1) = Application.WorksheetFunction.VLookup(Target, rng, 3, True)
Target.Offset(0, 2) = Application.WorksheetFunction.VLookup(Target, rng, 4, True)
Application.EnableEvents = True
End If

End Sub``````
This should automatically populae columns F and G as you enter data into column E.

#### ambz123

##### Board Regular
Hi thanks for the codes. However, right now I need to have more columns of data how do i change from the exisiting VBA code?

EPF Calculator.xlsm
ABCDEFGHIJKLM
1As of Jan 2020 As of Jan 2021 - Part A
211%7%12%9% As of Jan 2020 As of Jan 2021 - Part A
3Lower Range Higher Range Employer Monthly Contribution Employee Monthly Contribution Employer Monthly Contribution Employee Monthly Contribution To key in Actual Wages here Actual values for Employee Actual values for Employer Employee + Employer Contribution Actual values for Employee Actual values for Employer Employee + Employer Contribution
4MYR 1,020.00MYR 1,040.00MYR 136.00MYR 73.00\$ 136.00\$ 94.00MYR 1,044.55\$ 138.00\$ 75.00\$ 213.00
5MYR 1,040.00MYR 1,060.00MYR 138.00MYR 75.00\$ 138.00\$ 96.00MYR 1,080.00
6MYR 1,060.00MYR 1,080.00MYR 141.00MYR 76.00\$ 141.00\$ 98.00
7MYR 1,080.00MYR 1,100.00MYR 143.00MYR 77.00\$ 143.00\$ 99.00
8MYR 1,100.00MYR 1,120.00MYR 146.00MYR 79.00\$ 146.00\$ 101.00
9MYR 1,120.00MYR 1,140.00MYR 149.00MYR 80.00\$ 149.00\$ 103.00
10MYR 1,140.00MYR 1,160.00MYR 151.00MYR 82.00\$ 151.00\$ 105.00
11MYR 1,160.00MYR 1,180.00MYR 154.00MYR 83.00\$ 154.00\$ 107.00
12MYR 1,180.00MYR 1,200.00MYR 156.00MYR 84.00\$ 156.00\$ 108.00
13MYR 1,200.00MYR 1,220.00MYR 159.00MYR 86.00\$ 159.00\$ 110.00
14MYR 1,220.00MYR 1,240.00MYR 162.00MYR 87.00\$ 162.00\$ 112.00
15MYR 1,240.00MYR 1,260.00MYR 164.00MYR 89.00\$ 164.00\$ 114.00
16MYR 1,260.00MYR 1,280.00MYR 167.00MYR 90.00\$ 167.00\$ 116.00
17MYR 1,280.00MYR 1,300.00MYR 169.00MYR 91.00\$ 169.00\$ 117.00
18MYR 1,300.00MYR 1,320.00MYR 172.00MYR 93.00\$ 172.00\$ 119.00
19MYR 1,320.00MYR 1,340.00MYR 175.00MYR 94.00\$ 175.00\$ 121.00
Test
Cell Formulas
RangeFormula
H4H4=VLOOKUP(G4,\$A:\$F,3,TRUE)
I4I4=VLOOKUP(G4,\$A:\$F,4,TRUE)
J4J4=H4+I4
A5:A19A5=B4
B5:B19B5=A5+20
Named Ranges
NameRefers ToCells
_FilterDatabase=Test!\$A\$3:\$F\$352H4:I4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H4:I147Cell Value=0textNO

#### Joe4

I am really not sure what you are trying to do now (and it is frustrating when you craft a solution - and then they change the requirements on you), so I will explain the sections of the code you may need to change so you can edit it yourself.

The first, is to identify which column you are entering data in to trigger the code to run. That is this line of code here:
Rich (BB code):
``    If Target.Column = 5 And Target.Row >= 2 Then``
Originally it was column "E", which is the 5th column ("E" is the 5th letter of the alphabet).
So you would just change the number 5 to match whatever column data is being entered into.

Next, we need to define the range of our lookup table:
Rich (BB code):
``Set rng = Range("A2:D" & lr)``
Assuming that you are still starting in column A, I think the only thing you may need to change is your ending column ("D").

Then, you need to update the line of code that does the lookup and places the value in the appropriate cell:
Rich (BB code):
``Target.Offset(0, 1) = Application.WorksheetFunction.VLookup(Target, rng, 3, True)``
It is important to understand what "Target" is in these VBA procedures. "Target" is the range which triggers the code to run. So, in the first example, it was column E.
It is dynamic. So it was whatever cell in column E was just updated.

We wanted to place the first lookup in column F, so we started in the Target cell, and moved over one column to the right (that is what .Offset(0,1) does).
The format of Offset is Offset(rows, columns).

Judging from your original work you posted, I assume that you know how VLOOKUP works. So the only thing that should change in the formulas in the code is the third argument, which tell it which column from our lookup range to return (3rd in the line above).

In the original code I posted, we had to lines of code, to return the values to column F and G. To return more, just add similar ones, making the appropriate adjustment.

So based on the original code that I gave you, and the explanation above, you should be able to modify the code to do what you need.

1,129,915
Messages
5,638,970
Members
417,062
Latest member
Canucks21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back