When using:

=VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not found,
Excel is returning a 0 instead of the #N/A. This is causing me fits because
0 is a valid value and I therefore cannot sort out the 'not founds'.

I have verified that the cells are in fact empty, and do not contain zeros.
Why would excel be returning a 0 instead of #N/A?

Any help would be greatly appreciated.

Thanks,
Robert

=VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an
exact match is not found, Excel is returning a 0 instead of the #N/A

That's not possible. If the formula returns 0 it's finding an exact match
but if column 5 contains an empty cell then the result will be 0.

If you want the #N/A when column 5 is empty:

=IF(VLOOKUP(A5,'010425'!$A$5:$F$3368,5,0)="",#N/A,VLOOKUP(A5,'010425'!$A$5:$F$3368,5,0))

--
Biff
Microsoft Excel MVP


"RobertSD" wrote in message
...
When using:

=VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not
found,
Excel is returning a 0 instead of the #N/A. This is causing me fits
because
0 is a valid value and I therefore cannot sort out the 'not founds'.

I have verified that the cells are in fact empty, and do not contain
zeros.
Why would excel be returning a 0 instead of #N/A?

Any help would be greatly appreciated.

Thanks,
Robert

Hi,

Excel will return zero if it finds an exact match in colum 1 of the lookup
array and there is a zero or blank cell in column 5 of the lookup array.
Check you data again and I'm sure you'll find there is a match for A5.

Mike

"RobertSD" wrote:

When using:

=VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not found,
Excel is returning a 0 instead of the #N/A. This is causing me fits because
0 is a valid value and I therefore cannot sort out the 'not founds'.

I have verified that the cells are in fact empty, and do not contain zeros.
Why would excel be returning a 0 instead of #N/A?

Any help would be greatly appreciated.

Thanks,
Robert

Thanks for the reply, but this is not how VLOOKUP has been functioning for me
for the past 5 1/2 years (and we have been on 2007 for the past year). Until
now, every time excel found an exact match and the column I requested
contained an empty cell, the formula would return #N/A. This worked
perfectly for me because many of the cells contain data (including the value
0) while many others are empty. Now, are you telling me that when an exact
match is found and the column I'm requesting has a zero in it, excel will
return a zero, and when the column I'm requesting is empty, excel will return
a zero? An empty cell is not the same as a cell with the value 0 in it (a
cell with the value zero is not empty). How is that helpful if the formula
can't differentiate between an empty cell and zero? As it stands today, the
VLOOKUP function is no longer useful to me as a tool. I guarantee that I
have NEVER had to use any kind of IF statements with my VLOOKUPs, and I use
VLOOKUP a lot.

One important note: I just received a new laptop last week. What excel
setting or configuration could be different that would cause the different
functionality?

Thanks,
Robert

"T. Valko" wrote:

=VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an
exact match is not found, Excel is returning a 0 instead of the #N/A

That's not possible. If the formula returns 0 it's finding an exact match
but if column 5 contains an empty cell then the result will be 0.

If you want the #N/A when column 5 is empty:

=IF(VLOOKUP(A5,'010425'!$A$5:$F$3368,5,0)="",#N/A,VLOOKUP(A5,'010425'!$A$5:$F$3368,5,0))

--
Biff
Microsoft Excel MVP


"RobertSD" wrote in message
...
When using:

=VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not
found,
Excel is returning a 0 instead of the #N/A. This is causing me fits
because
0 is a valid value and I therefore cannot sort out the 'not founds'.

I have verified that the cells are in fact empty, and do not contain
zeros.
Why would excel be returning a 0 instead of #N/A?

Any help would be greatly appreciated.

Thanks,
Robert

"RobertSD" wrote in message
...
Thanks for the reply, but this is not how VLOOKUP has been functioning for
me
for the past 5 1/2 years (and we have been on 2007 for the past year).
Until
now, every time excel found an exact match and the column I requested
contained an empty cell, the formula would return #N/A. This worked
perfectly for me because many of the cells contain data (including the
value
0) while many others are empty. Now, are you telling me that when an
exact
match is found and the column I'm requesting has a zero in it, excel will
return a zero, and when the column I'm requesting is empty, excel will
return
a zero? An empty cell is not the same as a cell with the value 0 in it (a
cell with the value zero is not empty). How is that helpful if the
formula
can't differentiate between an empty cell and zero? As it stands today,
the
VLOOKUP function is no longer useful to me as a tool. I guarantee that I
have NEVER had to use any kind of IF statements with my VLOOKUPs, and I
use
VLOOKUP a lot.

One important note: I just received a new laptop last week. What excel
setting or configuration could be different that would cause the different
functionality?

Thanks,
Robert


Not possible, the only way you will get #N/A is when the lookup value does
not match in the 1st column , if the
cell that is returned from the 5th column is blank you will get zero and
that goes for all version that has VLOOKUP
as a function.

--


Regards,


Peo Sjoblom

I appreciate people trying to offer help, but essentially calling me a liar
isn't productive. What I'm describing IS POSSIBLE, and is exactly how it has
functioned for me for over 5 years - UNTIL I got this new laptop. I am not
smoking crack, I am not new to this, and I have used the described
funtionality a bazillion times (and could recite it back in my sleep).
Please don't try to make me believe I don't know what I'm talking about.

Is there anyone who can think outside the box enough to believe what I'm
saying is true, and then hopefully offer something helpful?

"Peo Sjoblom" wrote:


"RobertSD" wrote in message
...
Thanks for the reply, but this is not how VLOOKUP has been functioning for
me
for the past 5 1/2 years (and we have been on 2007 for the past year).
Until
now, every time excel found an exact match and the column I requested
contained an empty cell, the formula would return #N/A. This worked
perfectly for me because many of the cells contain data (including the
value
0) while many others are empty. Now, are you telling me that when an
exact
match is found and the column I'm requesting has a zero in it, excel will
return a zero, and when the column I'm requesting is empty, excel will
return
a zero? An empty cell is not the same as a cell with the value 0 in it (a
cell with the value zero is not empty). How is that helpful if the
formula
can't differentiate between an empty cell and zero? As it stands today,
the
VLOOKUP function is no longer useful to me as a tool. I guarantee that I
have NEVER had to use any kind of IF statements with my VLOOKUPs, and I
use
VLOOKUP a lot.

One important note: I just received a new laptop last week. What excel
setting or configuration could be different that would cause the different
functionality?

Thanks,
Robert


Not possible, the only way you will get #N/A is when the lookup value does
not match in the 1st column , if the
cell that is returned from the 5th column is blank you will get zero and
that goes for all version that has VLOOKUP
as a function.

--


Regards,


Peo Sjoblom

No need to get defensive!

If you've used VLOOKUP a bazillion times I can say with certainty that I've
used it a bazillion * bazillion and I know how it works.

You may be getting the results you say but it is *not possible* for VLOOKUP
to work the way you're describing. I suspect you have data that doesn't
quite "match" as you think it should or should not. For example, you want to
lookup the value in cell A1 which shows as 100. You have a 100 in your table
but you get #N/A as the result. One of the 100's is not exactly 100. One (or
both) may be the result of a formula and the displayed value is not the true
underlying value. The true underlying value of cell A1 may be 100.000002456
but it *displays* as 100 so you think the value is 100 and 100 should matche
100 in the table.

Other common causes are unseen non-printing characters that may be present
like leading/trailing spaces or data type mismatches where you may have TEXT
numbers being compared to NUMERIC numbers.

How is that helpful if the formula can't differentiate
between an empty cell and zero?

It's up to you to write the formula to take that into consideration if need
be.

An empty cell evaluates as 0 depending on what type of function/formula
you're using. Don't believe us? Make sure cell A1 is empty then enter this
formula in B1: =A1. What result did you get?

You'd be surprised at how many times Excel "humbled" experienced users
because the user thought they knew what they were doing! Myself included!
You have to start from the position that *Excel is always right* and find
your mistake. That mistake is often not understanding how Excel really
works.


--
Biff
Microsoft Excel MVP


"RobertSD" wrote in message
...
I appreciate people trying to offer help, but essentially calling me a liar
isn't productive. What I'm describing IS POSSIBLE, and is exactly how it
has
functioned for me for over 5 years - UNTIL I got this new laptop. I am
not
smoking crack, I am not new to this, and I have used the described
funtionality a bazillion times (and could recite it back in my sleep).
Please don't try to make me believe I don't know what I'm talking about.

Is there anyone who can think outside the box enough to believe what I'm
saying is true, and then hopefully offer something helpful?

"Peo Sjoblom" wrote:


"RobertSD" wrote in message
...
Thanks for the reply, but this is not how VLOOKUP has been functioning
for
me
for the past 5 1/2 years (and we have been on 2007 for the past year).
Until
now, every time excel found an exact match and the column I requested
contained an empty cell, the formula would return #N/A. This worked
perfectly for me because many of the cells contain data (including the
value
0) while many others are empty. Now, are you telling me that when an
exact
match is found and the column I'm requesting has a zero in it, excel
will
return a zero, and when the column I'm requesting is empty, excel will
return
a zero? An empty cell is not the same as a cell with the value 0 in it
(a
cell with the value zero is not empty). How is that helpful if the
formula
can't differentiate between an empty cell and zero? As it stands
today,
the
VLOOKUP function is no longer useful to me as a tool. I guarantee that
I
have NEVER had to use any kind of IF statements with my VLOOKUPs, and I
use
VLOOKUP a lot.

One important note: I just received a new laptop last week. What excel
setting or configuration could be different that would cause the
different
functionality?

Thanks,
Robert


Not possible, the only way you will get #N/A is when the lookup value
does
not match in the 1st column , if the
cell that is returned from the 5th column is blank you will get zero and
that goes for all version that has VLOOKUP
as a function.

--


Regards,


Peo Sjoblom

I didn't call you a liar, but if you insist that VLOOKUP works that way you
are either seriously
delusional or a straight face liar.

--


Regards,


Peo Sjoblom

"RobertSD" wrote in message
...
I appreciate people trying to offer help, but essentially calling me a liar
isn't productive. What I'm describing IS POSSIBLE, and is exactly how it
has
functioned for me for over 5 years - UNTIL I got this new laptop. I am
not
smoking crack, I am not new to this, and I have used the described
funtionality a bazillion times (and could recite it back in my sleep).
Please don't try to make me believe I don't know what I'm talking about.

Is there anyone who can think outside the box enough to believe what I'm
saying is true, and then hopefully offer something helpful?

"Peo Sjoblom" wrote:

If A5 is empty, then try toggling this setting:
(in xl2003 menus)

Tools|Options|Transition tab|Uncheck Transition Formula Evaluation
(I'd uncheck all those transition settings)

If you're using xl2007, I think it's buried under:
Office Button|Excel Options
Then I have no idea!



RobertSD wrote:

When using:

=VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not found,
Excel is returning a 0 instead of the #N/A. This is causing me fits because
0 is a valid value and I therefore cannot sort out the 'not founds'.

I have verified that the cells are in fact empty, and do not contain zeros.
Why would excel be returning a 0 instead of #N/A?

Any help would be greatly appreciated.

Thanks,
Robert

--

Dave Peterson