How to select the correct table for VLOOKUP to read from? (Payroll Taxes)

[pceleri]

Help! I have been developing a payroll environment in excel for my business to use. After overcoming several hurdles, I find myself stuck at one last one. In the workbook I have an Employee Info sheet wherein I contain indicators for their tax bracket (Married or Single) and the number of allowances, then I have two named tables (on a separate sheet) to use for the taxes "Married" and "Single". What I'm trying to do is create an equation that will read the indicator for which table to read from (Married or Single) and then calculate the tax withholding. How do I get VLOOKUP (or maybe I need a different function) to determine which table to use and then run the calculation?

Thanks ahead.

 

[Joe4]

Welcome to the Board!

Use an IF statements to decide which of two VLOOKUP formulas to use. It will be structured something like this:

=IF([I]some value[/I]="Married",VLOOKUP([I]for[/I] [I]married table[/I]),VLOOKUP([I]for[/I] [I]single table[/I]))

 

[pceleri]

I gave that a try using the equation that was at least calculating taxes correctly and got this -

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(EmployeeInfo!L4="Married",VLOOKUP(((BP7)-VLOOKUP(BP7,Married,4))*(VLOOKUP(BP7,Married,3, TRUE))+(VLOOKUP(BP7,Married,2))),VLOOKUP(((BP7)-VLOOKUP(BP7,Single,4))*(VLOOKUP(BP7,Single,3, TRUE))+(VLOOKUP(BP7,Single,2))))

However I get the error message "You've entered too few arguments for this function"

I'm far from an excel expert, so I appreciate any input and help.
[/FONT]

 

[Joe4]

Every VLOOKUP formula must have at least 3 arguments (it actually has 4, but the forth is options).
See: https://www.techonthenet.com/excel/formulas/vlookup.php

I really cannot make heads or tails of the formula that you posted. Can you explain the logic (in plain English) of how exactly you want this formula to work.
I don't understand why you have so many VLOOKUP parts to it, and this part looks very confusing and incorrect:

[COLOR=#333333]VLOOKUP(((BP7)-VLOOKUP(BP7,Married,4))[/COLOR]

 

[AhoyNC]

This link may also help.
https://www.youtube.com/watch?v=WogFiO3aOas

 

[pceleri]

I realized with your response that the equation means absolutely nothing without the context, I apologize. Within by workbook I have an EmployeeInfo sheet that contains the employee personal information as well as the few variables for payroll calculations - pay rate, tax filing status (married or single), tax withholding allowances and IRA deduction amounts. All of the individual employee's information is contained in one row and the information is called back to the calculation using VLOOKUP on an assigned employee ID #.

[/URL]

What I'm attempting to do is create an equation that reads the filing status (married or single), chooses the correct table to pull values from and then run the calculation for tax withholding. I have selected and named the tables appropriately (Married and Single) so that excel knows where to look.

[/URL]TaxTable by 626Media, on Flickr[/IMG]

In essence, these two sheets contain the data and variables to run the calculation in the monthly payroll sheet. So far most of the calculations work correctly using VLOOKUP with the employee ID number to populate the correct variables, but I have ran into a road block trying to choose the correct table and then calculate the withholding. The overall goal is to only select the employee ID to get the info and then enter only the hours worked and incidentals (reimbursements, bonuses, things that don't occur regularly) and let the worksheet do the calculations.

[/URL]PayrollMain by 626Media, on Flickr[/IMG]

I'm sure that there are easier and likely more efficient ways to accomplish what I'm after, however my Excel knowledge seems limited to just enough to make a broken workbook. I hope this gives a better idea of what I'm attempting. Thanks again.

 

[Joe4]

Forget about the choices for a minute.
Are you able to successfully create a VLOOKUP function that looks up what you need from the Married table (we are assuming that you are looking up a married person)?
Likewise, are you able to successfully create a VLOOKUP function that looks up what you need from the Single table (we are assuming that you are looking up a single person)?

If so, can you post those two separate VLOOKUP functions?

 

[pceleri]

Forget about the choices for a minute.
Are you able to successfully create a VLOOKUP function that looks up what you need from the Married table (we are assuming that you are looking up a married person)?
Likewise, are you able to successfully create a VLOOKUP function that looks up what you need from the Single table (we are assuming that you are looking up a single person)?

If so, can you post those two separate VLOOKUP functions?

Yes I am, they are as follows (I've replaced the cell names with a little more context)-

For married - [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=(((TaxableIncome)-VLOOKUP(TaxableIncome,Married,4))*(VLOOKUP(TaxableIncome,Married,3, TRUE))+(VLOOKUP(TaxableIncome,Married,2)))

For single - [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=(((

TaxableIncome​

)-VLOOKUP(

TaxableIncome​

,Single,4))*(VLOOKUP(

TaxableIncome​

,Single,3, TRUE))+(VLOOKUP(

TaxableIncome​

,Single,2)))

In English the equation would be ((TaxableIncome - MarginalIncome)xTaxPercentage))+BaseTax BUT I need someway for the equation to see and utilize the correct table.

As of right now I have a separate cell that calculates the taxable income with the individuals indicated allowances, and from there I am attempting to calculate the withholding. Currently I am using the percentage method to calculate the taxes, however after watching the video that was recommended I wonder if using the bracket method would be easier. I know that this is a messy calculation that I'm using, but for now it's returning accurate amounts.[/FONT]_{^{<strike>
</strike>}}[/FONT]

 

[Joe4]

OK, if you have those formulas working properly, there are two ways you could use them:

Store each calculation in a blank column somewhere (can even be hidden), and then call thre appropriate one.
So, say you store the Married calculation in column MA and then Single calculation in cell MB, then your formula to return the appropriate one can look like:

=IF(L4="M",MA4,MB4)

Or, if you do not want to store those calculations in another cell and do it all in one, just substitute your working formulas in the place of MA4 and MB4 (it will be a bit long, but should work).

 

[pceleri]

Fantastic. So that did work, I ended up with this -

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(EmployeeInfo!L4="M",(((BP7)-VLOOKUP(BP7,Married,4))*(VLOOKUP(BP7,Married,3,TRUE))+(VLOOKUP(BP7,Married,2))), (((BP7)-VLOOKUP(BP7,Single,4))*(VLOOKUP(BP7,Single,3,TRUE))+(VLOOKUP(BP7,Single,2))))

The thing is, the reason I've been trying to use VLOOKUP is that the L4 etc ties that particular cell to the equation, with employee turnover the positioning will move within the EmployeeInfo tab, what I'm hoping to do is instead of it looking at L4 it looks at the employee ID # and then finds the corresponding variable within the L column.

By the way, thanks for everything so far, it is greatly appreciated.
[/FONT]