New hire pay at Large Firm, Inc. is based on years of previous experience. Your boss wants a spreadsheet that gives him the new hire pay for a large set of people they are interviewing in the coming weeks. You are given a spreadsheet by the hiring manager that has two sheets. One sheet, Candidates, lists each candidate on their own row, which their years of relevant previous experience in column G. The second sheet, Pay Rate, gives the information about what a new hire would start at depending upon years of experience.

The first column of pay rate is a list of years from 1 to 10, and the second column is a list of pay rates. In other words, this data covers A2:B11. We have made a named range for this data, pay_rates. The first blank column on the Candidates sheet is column N, and this is where you are to list the pay rates. What function do you use? Write the function that would go in N2 before being copied down the rest of the column (data pictured below).

Candidates sheet 1 CandidateID Race GenderAge Local Yrs_Educ Yrs_Exp 1506 1568 1479 1136 1830 1867 1769 1385 1381 1059 1120 1229 1136 1586 1466 1539 37 29 38 35 38 28 27 49 41 46 50 10 10 10 12 13 10 15 16 17 37 26 30 34 Pay Rate sheet 1 Years_Exp Start_Pay 1 $65,500.00 2 $67,000.00 3 $68,500.00 4 $70,000.00 5 $71,500.00 6 $73,000.00 7 $74,500.00 8 $76,000.00 9 $77,500.00 10 $79,000.00 10