Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup formula using tax yables
I need a formula to caculate the amount of tax withholding using VLookup with
the following information.Name Sales "Base Salary" "Commis- sion" "Gross Pay" Exemp-tions S.S.T. F.W.T S.W.T. "Net Pay" "Percent of Total Sales" Balish $549.00 $240.00 2.45 $242.45 3 $17.34 Brown $543.00 $240.00 2.15 $242.15 2 $17.31 Colman $439.00 $240.00 0 $240.00 3 $17.16 Lewis $267.00 $240.00 0 $240.00 1 $17.16 Perrota $389.00 $240.00 0 $240.00 1 $17.16 Wendell $1,054.00 $240.00 27.7 $267.70 2 $19.14 Total $3,241.00 $1,440.00 $32.30 $1,472.30 $105.27 Average $540.17 Largest Smallest and the following tax tables FERERAL WITHHOLDING TAX TABLE(PARTIAL) "At Least" "BUT LESS THAN" EXEMPSTIONS 0 1 2 3 $236.00 $240.00 $42.80 $33.60 $24.40 $15.20 $240.00 $248.00 $44.00 $34.60 $25.40 $16.20 $248.00 $256.00 $45.40 $36.20 $26.80 $17.60 $256.00 $264.00 $46.80 $37.60 $28.40 $19.20 $264.00 $272.00 $48.20 $39.00 $29.80 $20.60 STATE WITHHOLDING TAX TABLR(PARTIAL) $230.00 $240.00 $6.20 $5.00 $4.00 $3.20 $240.00 $250.00 $6.60 $5.40 $4.40 $3.60 $250.00 $260.00 $7.00 $5.80 $4.80 $3.80 $260.00 $270.00 $7.60 $6.40 $5.20 $4.20 $270.00 $280.00 $8.00 $6.80 $5.60 $4.60 $280.00 $300.00 $8.80 $7.60 $6.40 $5.60 $300.00 $320.00 $9.60 $8.40 $7.20 $6.00 $320.00 $340.00 $10.80 $9.40 $8.00 $7.00 $340.00 $360.00 $11.80 $10.40 $9.00 $7.80 |
#2
|
|||
|
|||
One way:
Name the tables, (e.g., FedTable, StateTable) starting in the "But less than" column (e.g., B2:F6) by selecting them and entering the name in the Name Box at the left of the Formula Bar. =VLOOKUP(E2, FedTable, F2+2, TRUE) =VLOOKUP(E2, StateTable, F2+2, TRUE) In article , constance wrote: I need a formula to caculate the amount of tax withholding using VLookup with the following information.Name Sales "Base Salary" "Commis- sion" "Gross Pay" Exemp-tions S.S.T. F.W.T S.W.T. "Net Pay" "Percent of Total Sales" Balish $549.00 $240.00 2.45 $242.45 3 $17.34 Brown $543.00 $240.00 2.15 $242.15 2 $17.31 Colman $439.00 $240.00 0 $240.00 3 $17.16 Lewis $267.00 $240.00 0 $240.00 1 $17.16 Perrota $389.00 $240.00 0 $240.00 1 $17.16 Wendell $1,054.00 $240.00 27.7 $267.70 2 $19.14 Total $3,241.00 $1,440.00 $32.30 $1,472.30 $105.27 Average $540.17 Largest Smallest and the following tax tables FERERAL WITHHOLDING TAX TABLE(PARTIAL) "At Least" "BUT LESS THAN" EXEMPSTIONS 0 1 2 3 $236.00 $240.00 $42.80 $33.60 $24.40 $15.20 $240.00 $248.00 $44.00 $34.60 $25.40 $16.20 $248.00 $256.00 $45.40 $36.20 $26.80 $17.60 $256.00 $264.00 $46.80 $37.60 $28.40 $19.20 $264.00 $272.00 $48.20 $39.00 $29.80 $20.60 STATE WITHHOLDING TAX TABLR(PARTIAL) $230.00 $240.00 $6.20 $5.00 $4.00 $3.20 $240.00 $250.00 $6.60 $5.40 $4.40 $3.60 $250.00 $260.00 $7.00 $5.80 $4.80 $3.80 $260.00 $270.00 $7.60 $6.40 $5.20 $4.20 $270.00 $280.00 $8.00 $6.80 $5.60 $4.60 $280.00 $300.00 $8.80 $7.60 $6.40 $5.60 $300.00 $320.00 $9.60 $8.40 $7.20 $6.00 $320.00 $340.00 $10.80 $9.40 $8.00 $7.00 $340.00 $360.00 $11.80 $10.40 $9.00 $7.80 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions |