Vlookup quit working

  • Thread starter Thread starter Patti
  • Start date Start date
P

Patti

Any ideas why vlookup would start to fail sporadically after it has worked
reliably for several months? The only thing that has changed is that the
name range that I am referencing has grown to over 500 rows.

Here is was has been verified: The formula is still correct. The name
range includes all of the rows. The cell format is still text. The length
of the cell contents is correct (no padding). There are no duplicates in my
look-up range.

In one case, the correct item was returned only after I changed the sort in
the named range to ascending, even though I use the optional FALSE argument
in the formula. But other items are still not being returned.

In one case, the word "FALSE" was initially returned from one spot in the
work sheet, but if I tried the same criteria in another spot, it worked. I
again verified the formula. Oddly, when I recopied the formula from the
cell above - success. But recopying the formula is not working in any other
cases.. soooo odd.

At a total loss..appreciate the help!

Patti
 
Hi
best way: please post your formula :-)
Some ideas:
- is your calculation mode set to manual?
- are the values really EXACT matches (try comparing them manually with
something like =A1=C1 and test if this returns TRUE)

also what does VLOOKUP return now: what is 'not working': does it
return an error (if yes which error), or does it return wrong results
 
Please post your formula, search argument and some values before and after
the item looked for.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Thanks Frank,

I will post the formula tomorrow morning, since it is at work.

Calculation mode is not set to manual. Except for the one instance that it
returned the word "FALSE", it is not returning the wrong result. It just
isn't return any text. I have the conditional format set so that errors
don't show, so I will definitely check that. I'll also try your other
suggestion.

Thanks again.

Patti
 
Back
Top