Vlookup not working consistently

[Julie B.]

I've searched all through other posts and have not found anyone with a
similar problem. We have several workbooks that have been set up for at
least 3 years or more. Some of them suddenly, on any given line, will not
return the correct data even though it should be. At first it started with
just a line or two, and then slowly began to increase. At first we thought
the file was corrupt, so we re-created it. That didn't help. Now it has
started doing it in one of our other files--works fine, and then all of a
sudden starts returning zeros.

The formula I happen to be using right now is:
IF(ISERROR(VLOOKUP($A26,BUDGET,5,FALSE)),0,VLOOKUP($A26,BUDGET,5,FALSE))

Personally, I don't think it's a problem with the formula because the
formulas very from workbook to workbook. It can work fine on line 1 to 25,
quit working for 26 to 30, and then start working again on line 31. It's
bizarre.

Anyone have any ideas?

 

[Fred Smith]

If the formula is returning zero, it means the Vlookup is failing (ie, can't
find the contents of a26 in the table).

Some things to check for are:
1. Are there any trailing spaces or extra characters in $a26?
2. Is the Budget range defined properly?

Give us some examples of what's in A26 when it returns 0.

There's no reason the problem can't be tracked down.

Regards,
Fred.

 

[Julie B.]

You were correct--the budget range was not defined properly. There were a
substantial amount of new rows this time that were not being picked up due to
the incorrect range. I fixed it and it's working perfectly now. That is the
same problem that was on our other workbook as well.

Thank you SO MUCH!

 

[Fred Smith]

Glad to hear it's working. Thanks for the feedback.

Fred.