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.
The Solution
1.
Click on cell E2 and type the following formula that uses structured reference
=HLOOKUP ([@[Service Years]],$Q$15:$U$16,2)
Then press enter
If you insert A1:J31 as table, automatically the above formula will be applied to the range E3:E31.
Otherwise, fill the above formula to E3 to E31.
2.
Click on cell G2 and type the following formula that uses structured reference
=(2018-[@[First Aid Certificate Year]])
Then press enter
If you insert A1:J31 as table, automatically the above formula will be applied to the range G3:G31.
Otherwise, fill the above formula to G3 to G31.
