VLOOKUP: Why do I get "0" instead of #N/A or #REF???

G

Guest

I am running a VLOOKUP in a table with nearly 30,000 lines. Some of the
cells are kicking back valid data, some are kicking back #N/A (because there
is no corresponding infor to lookup), and then some are kicking back a 0
(zero) when 0 is not an option.

Why am I getting a 0? It does not say this is an error...
 
B

Bill Manville

What is the formula in the cells that give 0?
How is the table organised - is the first column sorted or not.

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
G

Guest

It's the same formula in all the cells: =VLOOKUP(B1327,'NGO
changes'!$C$2:$J$10487,6,FALSE)

The referenced table is nto sorted but it shouldn't have to be...it worked
for some cells and not others.
 
B

Bill Manville

No obvious reason.

I wonder if you have a circular reference that is preventing the
calculation chain from completing (so the 0s are not the result of the
formula at all).

Check the statusbar to see if it says "Circular".

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
G

Guest

Unexpected 0 is usually the result of VLOOKUP finding a blank cell in which
case it returns 0. Is this the case here?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Replace N/A error with zero (0) without losing VLookup function 1
VLookup with#N/A 1
Return 0 instead of #n/a 1
I need to write a formula for a VLOOKUP to put 0 instead of #N/A . 2
VLOOKUP - 0 instead of # N/A 8
Returning 0 instead of #N/A when no value is found 8
Return 0 instead of N/A 2
VLOOKUP returning #N/A result 2

Top