I have taken the time to type out in excel the circular E for weekly married and monthly married tax table for federal withholding. I have one employee that I have to do a weekly payroll check for and he also gets a monthly commission check based on sales. With that being said, I don't want to have to look up his withholding rate everytime I run payroll. I need to have excel look for the right amount based on the following info that is on the same sheet.
So if I have W in the first column, then excel needs to search the Weekly Withholding table and if it is M, then the monthly withholding table. However, my tax table has 2 values for wage range ... "at least this amount" and "but less than this amount". I have it searching the second value and looking for an approximate match that is less than.
I know how to create one formula to lookup the value with index/match, but I dont know how to make it "IF W then look at this table" or "if M then look at that table".
IF($AN13=0,0,INDEX(W_WH,MATCH($AN13,$Q$142:$Q$277,1),MATCH($M$6,$P$4:$AB$4)))
where
AN13 = salary payrrate
W_WH = range to index for weekly married wages
Q142:Q277 = greateer of the 2 wage values
M6 = # of withholding allowances on W-4 form for employee
P4:AB4 = # of withholding allowance header on table
The "IF AN13 = 0" is where I need excel to look at W/M column (starting in AF13) and determine if W then formula I listed above, but if M then this formula:
INDEX(M_WH,MATCH($AN13,$Q$6:$Q$141,1),MATCH($M$6,$P$4:$AB$4)))
Any ideas on how to insert that?
Thanks
Rachel V in Florida
So if I have W in the first column, then excel needs to search the Weekly Withholding table and if it is M, then the monthly withholding table. However, my tax table has 2 values for wage range ... "at least this amount" and "but less than this amount". I have it searching the second value and looking for an approximate match that is less than.
I know how to create one formula to lookup the value with index/match, but I dont know how to make it "IF W then look at this table" or "if M then look at that table".
IF($AN13=0,0,INDEX(W_WH,MATCH($AN13,$Q$142:$Q$277,1),MATCH($M$6,$P$4:$AB$4)))
where
AN13 = salary payrrate
W_WH = range to index for weekly married wages
Q142:Q277 = greateer of the 2 wage values
M6 = # of withholding allowances on W-4 form for employee
P4:AB4 = # of withholding allowance header on table
The "IF AN13 = 0" is where I need excel to look at W/M column (starting in AF13) and determine if W then formula I listed above, but if M then this formula:
INDEX(M_WH,MATCH($AN13,$Q$6:$Q$141,1),MATCH($M$6,$P$4:$AB$4)))
Any ideas on how to insert that?
Thanks
Rachel V in Florida
