Results 1 to 10 of 10

Thread: How to select the correct table for VLOOKUP to read from? (Payroll Taxes)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,717
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    Welcome to the Board!

    Use an IF statements to decide which of two VLOOKUP formulas to use. It will be structured something like this:
    Code:
    =IF(some value="Married",VLOOKUP(for married table),VLOOKUP(for single table))
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    =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.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,717
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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:
    Code:
    VLOOKUP(((BP7)-VLOOKUP(BP7,Married,4))
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,252
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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


  6. #6
    New Member
    Join Date
    Apr 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 #.[IMG]

    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.

    [IMG]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.

    [IMG]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.

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,717
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    New Member
    Join Date
    Apr 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Joe4 View Post
    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 - =(((TaxableIncome)-VLOOKUP(TaxableIncome,Married,4))*(VLOOKUP(TaxableIncome,Married,3, TRUE))+(VLOOKUP(TaxableIncome,Married,2)))

    For single - =(((
    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.


  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,717
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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:
    Code:
    =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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    New Member
    Join Date
    Apr 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    =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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •