# Help with nested functions

• 09-03-2018, 12:55 PM
m3d1c123
Help with nested functions
Enclosed is a copy of my current template I'm working on. Essentially I'm trying to create a spreasheet that compares gross earnings to the federal taxation index to auto populate how much federal and provincial tax to take off a pay check.

There are 2 parts to this - one I need to determine which index it falls within and two I need the formula to compare the Claim code against what the employee has on record and adjust the tax amount accordingly.
• 09-03-2018, 01:18 PM
AliGW
Re: Help with nested functions
Thanks for the workbook, but where are we looking (cell reference and tab, please)? And what exactly have you tried that is not working for you?
• 09-03-2018, 05:37 PM
m3d1c123
Re: Help with nested functions
In cell m4 on the third sheet. Under federal tax. I'm trying to find a way for the gross salary to compare itself to see where it falls under the index on the next page, once it determines that i want it to check which CC code the employee has to see how much tax they pay. I somehow managed to get it working, I think? I added a +4 at the end of the formula but I'm unsure why it actually works.

=(VLOOKUP([@[GROSS (\$)]],'Biweekly Federal Index'!\$A\$7:\$N\$38,(HLOOKUP('Employee Info'!\$K\$5,'Biweekly Federal Index'!\$D\$8:\$N\$38,1,TRUE)+4)))
• 09-03-2018, 06:12 PM
jtakw
Re: Help with nested functions
Hi,

The formula you posted above does work, but it's kind of a "round about" way of doing it.

Formula:
`=VLOOKUP([@[GROSS (\$)]],'Biweekly Federal Index'!\$A\$7:\$N\$38,MATCH('Employee Info'!\$K\$5,'Biweekly Federal Index'!\$A\$8:\$N\$8,0))`

See attached.
• 09-03-2018, 06:34 PM
m3d1c123
Re: Help with nested functions
Quote:

Originally Posted by jtakw
Hi,

The formula you posted above does work, but it's kind of a "round about" way of doing it.

Formula:
`=VLOOKUP([@[GROSS (\$)]],'Biweekly Federal Index'!\$A\$7:\$N\$38,MATCH('Employee Info'!\$K\$5,'Biweekly Federal Index'!\$A\$8:\$N\$8,0))`

See attached.

Thanks for replying. I've completed the index so its been updated since so I'll attach a new copy.

I am not able to get the formula you mentioned to work. It is included in the uploaded copy. Its giving me a result of 0.
• 09-03-2018, 06:53 PM
jtakw
Re: Help with nested functions
You have an incorrect Cell Range.

You have:

=VLOOKUP([@[GROSS (\$)]],'Biweekly Federal Index'!\$A\$8:\$N\$337,MATCH('Employee Info'!\$K\$5,'Biweekly Federal Index'!\$D\$7:\$N\$7,0))

It should be:

=VLOOKUP([@[GROSS (\$)]],'Biweekly Federal Index'!\$A\$8:\$N\$337,MATCH('Employee Info'!\$K\$5,'Biweekly Federal Index'!\$A\$7:\$N\$7,0))
• 09-03-2018, 06:57 PM
m3d1c123
Re: Help with nested functions
ohh shoot! thanks!
• 09-03-2018, 07:35 PM
jtakw
Re: Help with nested functions
You're welcome, thanks for the feedback, hope you get it working.
If you need additional help, post back.