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