In cell P2, use a VLOOKUP function to determine the annual cost of each employee's medical plan as described below: (a). Use a structured reference to the Medical Plan field. (b). Use the defined name Medical_Premium (which represents the range S8:T15) as the Table_Array parameter value. (c) To ensure accuracy, an exact match is required. Hint: The Medical Premium table array provides monthly premium amounts, not annual.

NZ =IF(M2>20,2,(IF(M2>15,1,0))) R S T P Medical Cost Vision Plan Cost - Variables: 401(k) Matching Plans FT PT 7.00% 4.00% Years service as of: 2014-09-01 1 1 2 3 4 5 6 7 8 9 10 11 12 13 A B Emp ID Last - Name - 1000 Florez 1001 Lane 1002 Edmonds 1003 Caldwell 1004 Crabble 1005 Monis 1006 Pugh 1007 Carter 1008 Fitzgerald 1009 Garrison 1010 Parker 1011 Ralston 1012 Drummon 1013 Yonker N Bonus Week Max 401(k) - Company Mate 2 2 2 2 2 1 1 1 1 1 1 1 1 840 360 540 840 360 540 840 540 840 840 360 840 540 840 840 Medical Plan Family High Family Low None Employee High EmployeeLow Spouse High Spouse Low Monthly Premium $ 1,400 S 900 $ $ 1,000 $ 500 $ 1,200 S 700 1 15 Cance Award NZ =IF(M2>20,2,(IF(M2>15,1,0))) R S T P Medical Cost Vision Plan Cost - Variables: 401(k) Matching Plans FT PT 7.00% 4.00% Years service as of: 2014-09-01 1 1 2 3 4 5 6 7 8 9 10 11 12 13 A B Emp ID Last - Name - 1000 Florez 1001 Lane 1002 Edmonds 1003 Caldwell 1004 Crabble 1005 Monis 1006 Pugh 1007 Carter 1008 Fitzgerald 1009 Garrison 1010 Parker 1011 Ralston 1012 Drummon 1013 Yonker N Bonus Week Max 401(k) - Company Mate 2 2 2 2 2 1 1 1 1 1 1 1 1 840 360 540 840 360 540 840 540 840 840 360 840 540 840 840 Medical Plan Family High Family Low None Employee High EmployeeLow Spouse High Spouse Low Monthly Premium $ 1,400 S 900 $ $ 1,000 $ 500 $ 1,200 S 700 1 15 Cance Award