Need help with If and LookUp forumula - example spreadsheet attached

**julie_cg** · 02-07-2012, 11:14 AM

I need some help on how to write a If and Look Up Formula. What I am trying to do is have 3 tables for weekly payroll. Federal - Single, Federal - Married and Virginia - All. The Main Spreasheet calculates their Gross Pay. Then The Look Up Formula needed would look at if they are Married or Single and use the Exemption # claimed on Main Spreadsheet and use the appropriate table to look up the correct withholding amount in the table. Need 2 forumlas - one to look up Federal WH and one to look up Virginia WH.

Can anyone help me with the proper formula needed please? I would greatly appreciate the help.

Payroll Calculator - for Formula.xlsx

**Chandrajit** · 02-07-2012, 11:31 AM

Hi Julie,
There are some questions.
1. The formula you need will be entered in cells D21 & D22 - what is 'Enter as negative number' ?
2. On the basis of what value/s in the sheet 'Master Pay Calc' should the formula lookup in the sheets 'Single Weekly', 'Married Weekly' & 'VA Weekly' for the required result?
3. The wage in sheet 'Master Pay Calc' is per hour and that in the other sheets do not seem so - you may please explain.

**julie_cg** · 02-07-2012, 11:49 AM

1) Yes - I need one formula to enter into D21 (orange) that will look up the Federal WH amount based on either table Single Weekly or Married Weekly. Which table to use is determined by if there is a M or and S in B9. What column to use in the table will read off the Gross Pay D17 for the Wages range in Col A and B in coulumn and the Exemption # is determined from C9 on Master Pay Calc and will determine which column in the appropriate table to use for col D to I. I just need a negative sign so the returned number will enter as a negative number to subtract from gross pay to get to net pay. If that is a biggie issue I can work around that with the Net Pay Formula.

2) For Federal - If they are Married (M) as determined by B9 - it will use table Married Weekly. If they are Single (S) as determined in B9 - it will use table Single Weekly. For Virginia - always use Table VA Weekly to determine D22 as Virginia does not destinquish between Married and Single. You do need to use Exemptions in C9 for Federal Table Lookup for Federal Forumula (D21) and Exemptions in C10 for Virginia Table Lookup for Virginia Formula (D22).

The Master Pay Calc will determine the Gross Pay for D17 via formula. You can just enter any # of hours in C14 as an example as that will give a $ amount in D17 via forumla already there. The D9 Total Gross Pay is the $ amount that is needed for all tables to determine which row to use based on the table enteries in column A and B of the tables.

Example if # hours was 40 and D17 Gross Pay was $230. Assume that the Person was M - 1 exemption for Federal and 1 exemption for Virginia. Than the Correct Answers for D21 Federal WH would be to use Table Married Weekly - and $11 Federal WH from E22 of that table. For Virginia WH D22 - the table to use would be VA Weekly and the correct VA WH would be $3 or E22 of that table.

Please let me know if still unclear. Appreciate the help.

**Chandrajit** · 02-07-2012, 02:18 PM

Hi Julie,

If I have interpreted your explanations correctly, the attached file may be your result. Please check and let me know if this helps or else, point out where I am wrong.