vlookup returns the same value above a certain first match

**lrpainting** · 06-04-2012, 11:44 AM

Hello,
I have my employee payroll in a spreadsheet. Each employee has a sheet with their hours and the deductions to withhold for the number of hours they worked. The vlookup function returns the same value for the "first match" 45 and above.
I will attach an example below.

Employee Payrollhelp.xlsx

**Spencer101** · 06-04-2012, 11:59 AM

The data range called "Chuck" only goes down to row 72 on the relevant tab.

Press "Ctrl+F3" to open the data range manager and change the 72 at the end of the range to 80 and see if that works for you.

EDIT: Sorry, Ctrl+F3 not Alt+F3

**Marcol** · 06-04-2012, 02:00 PM

Maybe you are going about this the hard way.

Try this ...
Make a dynamic table of Employee Details "EmpDets"
Use this for your lookup calculations, then there is no need to have a "ready reckoner" sheet for each employee.

I don't know your tax system, so some of the details I have used might be constants, and therefore simplify some of the formulae.
You should get the idea.

**lrpainting** · 06-04-2012, 04:34 PM

Thank you Spencer101,
I forgot to make the array larger when i added overtime.

Marcol,
Thank you as well. I have an excel formula book and it's going to take me a while to figure out those large formulas, but it seems a much cleaner way to do this