An #N/A returns in Vlookup formula

[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

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Guest]

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)

 

[Ken Wright]

1.- The lookup table must be sorted by the left most column; therefore I

would try sorting

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
------------------------------­------------------------------­----------------

 

[Guest]

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.

 

[Peo Sjoblom]

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

 

[George Nicholson]

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.

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,

 

[Ken Wright]

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

Best Wishes
ken....................

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Harlan Grove]

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.

....

There are even some bit & pieces that are just plain wrong.

 

[Harlan Grove]

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.

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.

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.

 

[Ken Wright]

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.....................