1. In cell E2, enter a formula using the HLOOKUP function to determine a staff m
ID: 3915403 • Letter: 1
Question
1. In cell E2, enter a formula using the HLOOKUP function to determine a staff member’s base salary (which is based on the number of years of service).
a. Use a structured reference to look up the value in the Service Years column. Retrieve the value in the 2nd row of the table in the range Q15:U16. Since base salary is tiered based on the number of years of service, find an approximate match.
b. Fill the formula into the range E3:E31, if necessary.
2. In cell G2, enter a formula without a function using structured references to determine the number of years since Adam Moriarty received his last First Aid Certification. The formula should subtract the value shown in the First Aid Certification Year column from 2018 and should automatically fill to the range G3:G31.
since First Aid CollegeLeadership Training Certification Staff ID Name Service Years Base Salary First Aid Certification Year Leadership Training Transportation Group Leader 1103 Adam Moria 1053 Artie Jimenez 1056 Bo Peterson 1035 Claire Hunter 1094 Eileen Randall 1066 Flossie Frampton 1050 Grace Richards 1054 Hayley Jin 1070 Hiro Marumi 1040 Isaac Runyon 1010 Isabella Thorne 1073 James Gutierrez 1069 Joey Stockton 1060 Julio deSouza 1043 Kira Weston 1034 Liam Swanson 1084 Maria Flatle 1078 Melinda Masterson 1036 Oscar Alberts 1074 Philip Jones 1076 Richard Tepper 1009 Robert Miller 1089 Sarah Winters 1022 Sienna Shapino 1033 Stephanie Ito 1065 Stephen McAllister 1078 Sylvia Lee 1048 Tanya Oldman 1053 Tristan Reinholt 1064 Walter Kamins 2017 2015 2013 2016 2013 2016 2017 2013 2013 2016 2017 2014 2015 2013 2015 2016 2017 2017 2015 2015 2014 2014 2015 2016 2016 2017 2013 2013 2014 2013 Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes 2 Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes YesExplanation / Answer
1A.
In E2 enter the formulas as '=HLOOKUP($D2,$Q$15:$U$16,2,TRUE)' where d2 is the serivce, q15:u16 is the range of cells containing the values, and since approximate match is asked - we use TRUE.
1B.
Copy the fomula from E2 to E31 - since we used '$' dollar signs - this is a absolute reference - which means the address references will not changed when copied down.
2.
In cell G2 enter the formula as'=2018-$F2' - which is where we are subtracting 2018 from the first aid year to give the number of years. Since this is a table - the formula would be automatically applied to remaining cells in the column, else copy or drag the formula to G3:G31
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.