Index Match to replace a Vlookup (1 Viewer)

raziel3

Registered User.
Local time
Today, 13:32
Joined
Oct 5, 2017
Messages
273
Hello,
I have a table that stores rates for NI Contributions called Rates

Rate Table.jpg

and I have been using a Vlookup to return the Rate based on the employee Salary like this

Code:
VLOOKUP(I4,'NIS AND TAX TABLE'!$E$2:$F$18,2).
Now that the rates have been increased (highlighted in the Rates Table attachment), how to get the new rates if I update the current Rate Table using the Effective Date. I've tried this:

Code:
{=INDEX(Rates,MATCH([@[PAY PERIOD END]]&AGGREGATE(14,6,Rates[EFFECTIVE DATE]/((Rates[MONTHLY SALARY]<=[@BASIC])*(Rates[EFFECTIVE DATE]<=[@[PAY PERIOD END]])),1),INDEX(Rates[MONTHLY SALARY],0),0))}

but keeps getting an #N/A
Current Results.jpg
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:32
Joined
Jul 9, 2003
Messages
16,244
I notice you have yet to receive a reply. Hence I'm bumping it up the list. Merry Christmas!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:32
Joined
May 7, 2009
Messages
19,169
this has been very long.
create a function in a module:
Code:
Public Function fnEffectiveRate(salary As Variant, columnName As String) As Double

Dim cn As Object
Dim rs As Object

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

' create temporary name for sheet with SPACE on their name
ThisWorkbook.Names.Add Name:="TEMP_", RefersTo:=Sheets("NIS AND TAX TABLE").Range(Sheets("NIS AND TAX TABLE").UsedRange.Address)

salary = Val(salary & "")
strSQL = "SELECT TOP 1 T1.[" & columnName & "] FROM TEMP_ AS T1 " & _
            "WHERE T1.[Effective Date] <= #" & Format(Date, "mm/dd/yyyy") & "# " & _
            "AND T1.[Monthly Salary] >= " & salary & " " & _
            "ORDER BY [Effective Date] DESC, [Monthly Salary] ASC;"


rs.Open strSQL, cn, 2 'adOpenDynamic

If rs.EOF = False Then
    rs.MoveLast
    rs.MoveFirst
    fnEffectiveRate = rs(0).Value
End If

rs.Close
Set rs = Nothing

' remove the temporary name
ThisWorkbook.Names.Item("TEMP_").Delete


End Function

do use it in your worksheet:
Code:
=fnEffectiveRate(I4, "Employee Contribution")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:32
Joined
May 7, 2009
Messages
19,169
modified it a bit:
Code:
Public Function fnEffectiveRate(salary As Variant, columnName As String) As Double

Dim cn As Object
Dim rs As Object
Dim strFile As String

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

' create temporary name for sheet with SPACE on their name
ThisWorkbook.Names.Add Name:="TEMP_", RefersTo:=ThisWorkbook.Sheets("NIS AND TAX TABLE").UsedRange

salary = Val(salary & "")

strSQL = "SELECT TOP 1 T1.[" & columnName & "] FROM TEMP_ AS T1 " & _
            "WHERE T1.[Monthly Salary] <= " & salary & " " & _
            "ORDER BY [Monthly Salary] DESC, [Effective Date] DESC;"

rs.Open strSQL, cn, 2 'adOpenDynamic

If rs.EOF = False Then
    rs.MoveLast
    rs.MoveFirst
    fnEffectiveRate = rs(0).Value
End If

rs.Close
Set rs = Nothing

' remove the temporary name
ThisWorkbook.Names.Item("TEMP_").Delete


End Function
 

raziel3

Registered User.
Local time
Today, 13:32
Joined
Oct 5, 2017
Messages
273
arnelgp I tried your code but it returns #VALUE. I had this code from an Access database you had helped me with in the past that worked very well, is there anyway you can modify it for Excel?

The table in the Excel workbook with contributions is called "NIS"

Code:
Public Function EmpNIS(ByVal Gross As Double, ByVal PeriodEnd As Variant) As Variant

Dim strPeriodEnd As String
Dim strEmpNIS As String

PeriodEnd = CDate(Nz(PeriodEnd, 0))

strPeriodEnd = "#" & Format(PeriodEnd, "mm/dd/yyyy") & "#"
strEmpNIS = "SELECT TOP 1 [Employee NIS] FROM NIS WHERE [Effective Date] <= " & strPeriodEnd & " And Range <= " & Gross _
            & " ORDER BY [Effective Date] DESC , Range DESC"

    With CurrentDb.OpenRecordset(strEmpNIS)
        If Not (.BOF And .EOF) Then
            EmpNIS = .Fields(0)
        End If
    End With

End Function

Results:
EmpNIS([Gross],[Period End])
EmpNIS(6200,1/31/18) = 61.40
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:32
Joined
May 7, 2009
Messages
19,169
try this please.
 

Attachments

  • Query.xls
    58.5 KB · Views: 137

raziel3

Registered User.
Local time
Today, 13:32
Joined
Oct 5, 2017
Messages
273
arnelgp I think i followed your instructions to a T but still getting #VALUE what can be wrong?. I'm using Excel 2016.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:32
Joined
May 7, 2009
Messages
19,169
can you upload the workbk and make a textbox for any instruction. sorry i am unable to answer, we dont have net at work.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:32
Joined
May 7, 2009
Messages
19,169
hi again, please find the attached.
let me know if it worked.
 

Attachments

  • Payroll v1.0.zip
    178.1 KB · Views: 163

raziel3

Registered User.
Local time
Today, 13:32
Joined
Oct 5, 2017
Messages
273
Hi. It's not working. For the first employee ADESH MATURA his Gross is 6000 so if the pay period was:-
- 12/1/18 NIS should be 61.40
- 1/1/19 NIS should be 70.00
but it's returning 70.00 no matter what pay period it was.

I've updated the NIS table to include the new rates, so employees paid before 12/20/18 will use the rate schedule where the EFFECTIVE DATE is 9/3/16. Employees being paid after the 12/20/18 will use the rates where the EFFECTIVE DATE is 12/20/18.

View attachment Payroll v1.1.xlsm.zip
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:32
Joined
May 7, 2009
Messages
19,169
For ADESH MATURA
- 12/1/18 NIS, I am getting 61.40 on the new code.
- 1/1/19, (last row on worksheet) I am getting 56.60. plz verify on your table if this is correct.
 

Attachments

  • Payroll v1.1.xlsm.zip
    175.6 KB · Views: 147

raziel3

Registered User.
Local time
Today, 13:32
Joined
Oct 5, 2017
Messages
273
For ADESH MATURA
- 12/1/18 NIS, I am getting 61.40 on the new code.
- 1/1/19, (last row on worksheet) I am getting 56.60. plz verify on your table if this is correct.

Yes that's the correct values.

One small thing though, The Effective Date for the new rates is 20/12/18. When I set the Pay Period End to 21/12/18 it returns the old rates (Rates where Effective Dates = 3/9/16), it should return the rates for 20/12/18. But if I set pay period to 1/1/19 I get the correct NIS.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:32
Joined
May 7, 2009
Messages
19,169
sorry, i am using the Pay Period Start as the basis of calculation. if during the work period the rate has changed, they will have to wait for the next payroll for the new rate to apply.
 

Users who are viewing this thread

Top Bottom