Certain rows of a VLOOKUP table are returning as N/A. but why?

**Solidstan** · 01-26-2013, 04:53 PM

Certain rows of a VLOOKUP table are returning as N/A, I have spent much time trying to find this fault with no success however I believe this is a problem with the table rather than the function, BUT of course I could be wrong.

This problem is on the "ROTA TABLE" sheet of my workbook column A. This column has the same function filled down through Column A. And this function BASICALLY looks up a VLOOKUP table on sheet "Drivers" in the same workbook. Changing the date within this workbook shifts the drivers list down 1 row per week, and that is basically the function.

The "show calculation steps" of this error looks pretty simple, but I cant understand why this error is appearing here. And only with certain drivers from the "drivers" list in which ever positions they appear.

If I could get help with this, it would be so much appreciated..

Many Many thanks in advance.
6th Jan 2013 -Practice Copy.xlsx

**dredwolf** · 01-26-2013, 05:39 PM

Try modifying your formula in A5 Rota table to this :

Formula:

Please Login or Register  to view this content.

Or this:

Formula:

Please Login or Register  to view this content.

the old formula seemed to calculate a decimal, which of course isn't in your lookup, so...#N/A

The first way insures it is a Integer number, the second will look for an approximate match..= or < than the calculated number, so if your number was 48.2333, it will find 48, when set to false, the number has to match EXACTLY..

Hope this helps

**Solidstan** · 01-26-2013, 06:13 PM

DREDWOLF, thank you hugely for you cleverness. I have spent weeks looking for this and your decimal answer was what I was missing

and you found it in minutes..
However for your second solution, the "TRUE" value is causing these decimal values to round down, just as you described. But this causes the driver name to be repeated at these locations(decimal locations).
Your first solution is mighty close to what I need, however it is causing the last position of the "INT cog" ( if I may call it that) to be a ZERO instead of a 1 and therefore the VLOOKUP fails here.

Any ideas on how I may fix this final minor fault.

P.S. your input was amazing, I really really are grateful for your help.

**dredwolf** · 01-26-2013, 06:22 PM

What date does this happen with?, I'm assuming the driver who gets 0 is the first one in the list on drivers sheet ?

**Solidstan** · 01-26-2013, 06:30 PM

Originally Posted by dredwolf

What date does this happen with?, I'm assuming the driver who gets 0 is the first one in the list on drivers sheet ?

This happens on all dates, and its the last driver position in the list (driver 55).
However thanks again for your help, I have changed driver 55 to driver ZERO.(on sheet "drivers") and my problem is solved.
THANKS MILLIONS!!!

**dredwolf** · 01-26-2013, 06:46 PM

You are welcome !

I was about to tell you that there was not much we could do as your formula always has 1 way to wind up @ zero, but looks like you hit an even simpler solution

Congrats ! And Thank You for the star tap, much appreciated !