An #N/A returns in Vlookup formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

For simplicity, I have the following vlookup formula listed below, where the
first number would be in cell A3 and the range I'm trying to find the number
is in cell A5:B5, and the result is in C5. However, I keep getting an #N/A
for my response. I have formatted the entire worksheet and all numbers as a
number and not text. This only happens to several rows in my hugh spreadheet.
The other formulas work. Any idea why this is happening to these several
cells?

63311

63311 40813 #N/A =VLOOKUP(A3,$A$5:$B$5,2,FALSE)


Thanks,
Mike
 
The data is likely not a match, regardless of what you think you are seeing.
Easy test is to find the entry you are looking up (Assume cell B2) and then
find where you think the matching cell is (Assume H99), and then in any
other cell put =B2=H99 and see if you get TRUE or FALSE as a result. If it
doesn't match, then use =ISNUMBER or =ISTEXT on each to see if they really
are what you thought they were.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Two things come to mind:
1.- The lookup table must be sorted by the left most column; therefore I
would try sorting
2.- If there is a blank space after 63311 or before it, the lookup will
fail, what I usually do is:
=Vlookup(Trim(A3),$A$5:$B$5,2,FALSE)
 
1.- The lookup table must be sorted by the left most column; therefore I
would try sorting
Click to expand...

Not if as per the Ops and your own example, the optional argument of FALSE
or 0 is included at the end of the formula.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
If you read the first paragraph of the Arguments window (while inserting the
function using the insert formula option), it clearly states that by default
the table must be sorted in ascending order.
On the range lookup argument, the false is used to return an exact match.

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.
 
You are wrong, read help. When VLOOKUP looks for an exact match there is no
need
to sort anything. Since the OP used FALSE in his formula the reason he gets
an error is simply a mismatch. Leading/trailing spaces, hidden html
characters or numbers formatted as text.



--
Regards,

Peo Sjoblom
 
Yes, False is used to return an exact match.

But Sorted data is only required when looking for an *approximate* match
(which the OP is not doing).

(When looking for an exact match, why would Excel need the data need to be
sorted? It either finds it or it doesn't).
by default the table must be sorted in ascending order.
Click to expand...
Yep, but that's because True is the default for RangeLookup. Once you change
RangeLooku to False, the "default" sort-order rule no longer applies.

HTH,
 
Hey stranger, hope all's well with you [ and couldn't have said it better
myself :-) ]

Best Wishes
ken....................
 
Admittedly not all of MS instructions are totally crystal, and do allow for
some interpretation, but in this case it really is exactly as I said.

Same also holds true for functions such as MATCH()

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Easiest way to check of course is simply to try it and see

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Ken Wright said:
Admittedly not all of MS instructions are totally crystal, and do allow
for some interpretation, but in this case it really is exactly as I said.
Click to expand...
....

There are even some bit & pieces that are just plain wrong.
 
Michael said:
If you read the first paragraph of the Arguments window (while inserting
the function using the insert formula option), it clearly states that by
default the table must be sorted in ascending order.
Click to expand...

And whatcha suppose the 'by default' qualification means? One interpretation
would be when using VLOOKUP with the DEFAULT 4th argument value TRUE. In
that case, the first column needs to be in ascending order or VLOOKUP's
result isn't reliable. (As long as VLOOKUP's 1st argument is equal to or
greater than the topmost value in the 1st column of its 2nd argument,
VLOOKUP will return the corresponding value from the column given by its 3rd
argument.)
On the range lookup argument, the false is used to return an exact match.
Click to expand...

Yes, and when using exact matches, so with VLOOKUP's 4th argument FALSE or
0, so NOT the default value, VLOOKUP performs a linear search through the
entire 1st column of its 2nd argument, and in that case that 1st column NEED
NOT BE SORTED.

Further, if there could be multiple instances of the same value in the 1st
column of VLOOKUP's 2nd argument, and one wants to find the 1st/topmost
instance in that column, it's necessary to use exact matching.
 
LOL - Say it aint so.........I can't believe such a thing could be true
:-)

Long time no speak, but best wishes from this side of the pond Harlan.

Regards
Ken.....................
 
Back
Top