1 Attachment(s)

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.

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?

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

1 Attachment(s)

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.

Try this instead:

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.

1 Attachment(s)

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.

Try this instead:

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.

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

Re: Help with nested functions

Re: Help with nested functions

You're welcome, thanks for the feedback, hope you get it working.

If you need additional help, post back.