• Ivy Tech Community College, Indianapolis

• EXCEL 2016

New Perspectives Excel 2016 Module 8: SAM Project 1b

Answer Key

GE ver. 6.51

3. "In cell E2, enter a formula using the HLOOKUP function and structured references to determine a staff member's pay rate (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 staff members do not receive a raise after every year of service, find an approximate match.

b. Fill the formula into the range E3:E31 if necessary."

Create a formula using structured references.

"A structured reference is just a different way to reference cells in a section of a spreadsheet that is formatted in a table. There are two ways to complete this, you can either click on the cell or type it in. This concept is NOT on the MOS exam. I will manually override grades where the rest of the formula is correct.

The solution for this step is: =HLOOKUP([Service Years],$Q$15:$U$16,2,TRUE)

The HLOOKUP is a formula to look up a value in a HORIZONTAL table. This works similar to the VLOOKUP. There are 4 parts to the formula. Lookup Value, Table Range to look in, row to look in for the value, and then if you want an exact or approximate match.

So, the question tells us to look up the value in the Service year column, so you can either type [Service Years] or simply click in cell D2. The question also tells you the second and third part of the formula. It states to look up the 2nd row in the table Q15:U16.