Change hourly rate wihout affecting previous table entries VLOOKUP

I have searched for two weeks, and can't seem to find the solution to this problem. It seems there is likely and easy solution, yet somehow it is escaping me.

I have a spreadsheet set up with a data table, from which I generate a pivot table, to track overtime costs. The data table has a column which contains a VLOOKUP that pulls the overtime rate from a named range on a separate worksheet, based on the employee's last name.

Occassionally, due to promotions and /or contractual pay increases (steps), I need to increase the hourly rate for an employee. Of course, when I do this, the VLOOKUP changes all of the previous entries in the data table for that employee to reflect the new rate. This obviously makes my overtime stats inaccurate.

How can I update the employee's hourly rate in the named range, without affecting the previous (already entered) data table entries??

Any help is greatly appreciated.

Mike

Thanks Ron for the quick reply... crazy week and I wasn't able to check until today.

I already have a date field in the data table. When overtime is worked, it is entered into the table along with the date it was worked. I would, however, appreciate any help you could give with constructing the formula. I'm not clear on how to add the effective date to the pay rate range. Should it be a new column, or as a new row beneath the employee name?