Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-19-2018, 04:55 AM   #1
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 58
Thanks: 19
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Index Match to replace a Vlookup

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 by raziel3; 12-19-2018 at 06:09 AM.
raziel3 is offline   Reply With Quote
Old 12-27-2018, 02:47 PM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,962
Thanks: 454
Thanked 881 Times in 837 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Index Match to replace a Vlookup

I notice you have yet to receive a reply. Hence I'm bumping it up the list. Merry Christmas!
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
raziel3 (12-28-2018)
Old 12-31-2018, 09:27 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Index Match to replace a Vlookup

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")

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 12-31-2018, 07:25 PM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Index Match to replace a Vlookup

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-10-2019, 07:36 AM   #5
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 58
Thanks: 19
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Index Match to replace a Vlookup

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
raziel3 is offline   Reply With Quote
Old 01-10-2019, 09:05 AM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Index Match to replace a Vlookup

try this please.
Attached Files
File Type: xls Query.xls (58.5 KB, 42 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-11-2019, 04:35 AM   #7
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 58
Thanks: 19
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Index Match to replace a Vlookup

arnelgp I think i followed your instructions to a T but still getting #VALUE what can be wrong?. I'm using Excel 2016.

raziel3 is offline   Reply With Quote
Old 01-11-2019, 08:53 AM   #8
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Index Match to replace a Vlookup

can you upload the workbk and make a textbox for any instruction. sorry i am unable to answer, we dont have net at work.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-11-2019, 10:34 AM   #9
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 58
Thanks: 19
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Index Match to replace a Vlookup

The calculations are being done on the Details sheet. Payroll v1.0.zip
raziel3 is offline   Reply With Quote
Old 01-12-2019, 09:22 AM   #10
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Index Match to replace a Vlookup

hi again, please find the attached.
let me know if it worked.
Attached Files
File Type: zip Payroll v1.0.zip (178.1 KB, 41 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
raziel3 (01-14-2019)
Old 01-12-2019, 12:10 PM   #11
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 58
Thanks: 19
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Index Match to replace a Vlookup

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.

Payroll v1.1.xlsm.zip
raziel3 is offline   Reply With Quote
Old 01-13-2019, 09:07 AM   #12
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Index Match to replace a Vlookup

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.
Attached Files
File Type: zip Payroll v1.1.xlsm.zip (175.6 KB, 37 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-14-2019, 08:55 AM   #13
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 58
Thanks: 19
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Index Match to replace a Vlookup

Quote:
Originally Posted by arnelgp View Post
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.
raziel3 is offline   Reply With Quote
Old 01-14-2019, 10:49 AM   #14
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Index Match to replace a Vlookup

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.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
raziel3 (01-14-2019)
Reply

Tags
excel , excel 2010 , excel 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Accomplish an Approximate Match Vlookup in Access Robecca Queries 5 10-18-2016 03:10 PM
Newbie Question - Match Function (i.e. VLOOKUP in Excel) golfnut324 Queries 5 01-07-2009 09:57 AM
Vlookup vs. Index/Match Spence Excel 6 11-26-2008 03:14 PM
Index , Match mystery scott-atkinson Excel 1 02-28-2008 11:05 AM
Match data across tables... Vlookup? mlopes1 General 1 02-17-2003 08:20 AM




All times are GMT -8. The time now is 01:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World