Vlookup returning #N/A when it should not

  • Thread starter Thread starter robs3131
  • Start date Start date
R

robs3131

Hi all,

Per the subject, vlookup is not working -- I thought it might be because of
hidden values as the data was downloaded from a website (in Excel format
though), however, I used David McRitchie's TRIMALL macro, but the problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as well as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be sure
that it should work, I copied one of the lookup values, went to the column of
the sheet where vlookup was searching, and used CTRL+F to validate that the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
 
Can you show a copy of the formula you are using? And it would also help to
describe your data layout - your post implies an earlier thread, but I don't
remember seeing it.

Pete
 
Hi Robert,

One possibility:

If the lookup number is, for example: 2 and the values in the table are 2.1,
2.2 etc. you will get the #NA error. The exanmple above would behave exactly
as you described in your post. Is it possible the number is something like:
2.0000000001?

To test this, copy your lookup number into the table, I suspect the #NA will
go away.

Good luck

Mike
 
It's almost always something like a space at the end of the cell contents...
if you have a value like "abcd " (note space after d), and you control-F
"abcd", it will find that cell. But a VLOOKUP on "abcd" won't...

After you do the control-F to find the cell, then F2 edit to see if it
contains any spaces at the end...
 
After looking at it, it appears that the values being looked up are numbers
(when I highlight them all, the "sum" function on the bottom Excel bar shows
a sum) while the source vlookup values are not numbers (nothing shows in the
bottom Excel bar when I highlight these values).

It appears that highlighting the source vlookup numbers and using "Format"
from the Excel format to format these values as numbers does not work as they
still do not show a sum. Also, I used an If/Then to validate that that
source vlookup values and the values being looked up are not equal. Directly
below shows the spreadsheet with it's formulas while below that are the
results. Any help is greatly appreciated - I've spent way too long trying to
figure this out :)

Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 =VLOOKUP(C2,A:B,1,FALSE) =IF(A2=C2,"","issue")
720062194 720062194 =VLOOKUP(C3,A:B,1,FALSE) =IF(A3=C3,"","issue")
720062860 720062860 =VLOOKUP(C4,A:B,1,FALSE) =IF(A4=C4,"","issue")
720063184 720063184 =VLOOKUP(C5,A:B,1,FALSE) =IF(A5=C5,"","issue")


Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 #N/A issue
720062194 720062194 #N/A issue
720062860 720062860 #N/A issue
720063184 720063184 #N/A issue
 
The cell values have to match exactly, including format, for your VLOOKUPs
to work. However, you can achieve this quite easily within the formula
without having to change a lot of values. If column A contains proper
numbers and column C has "text" numbers, then change your formula to this:

=VLOOKUP(C2*1,A:B,1,FALSE)

The *1 will force the value in C2 to be treated as a number.

If you have text values in column A and proper numbers in column C, then
make this change:

=VLOOKUP(C2&"",A:B,1,FALSE)

Hope this helps.

Pete
 
You can find out quickly if your values match. Suppose your lookup value is
in A1and your lookup table is in B1:C10 and you think the value in A1 should
match the value in B6. Try the formula =A1=B6. If that returns TRUE your
values match, if it returns FALSE, they don't

Tyro
 
Thanks Pete! That did it -- I input the *1 into the vlookup and it worked.
So frustrating that it just comes down to something so simple after so many
hours spent looking into this... :)
 
Glad to hear it worked for you.

Sometimes the most frustrating problems have an easy solution.

Pete
 
I am still finding my vlookup will not return a value for some odd lines of
my data, even though I know that the data is there in the array. I have been
having issues with various formats, and am using vlookup(A1+0, ... or
vlookup(A1&"", ... to force the vlookup to search for number or text values,
and generally this works.

However, I still find some odd lines return an N/A even if I copy the cell
from the search list to the table array - and use =A1=D5 which returns TRUE
showing they are definitely the same.

So is there anything else that could be causing this error?

Thanks for reading...
 
Always post your formula
If the 4th argument of the VLOOKUP is omitted or TRUE, the list has to be
sorted ascending
 
One cell has this formula:

=VLOOKUP(A254,'Tables Removed'!A1:A582,1)

it returns the value in A254, which is natched in that range, correctly.

If I try to cover the entire range (up to row 595)
I get #N/A

Oddly, the following return #N/A as well:

=VLOOKUP(A254,'Tables Removed'!A2:A582,1)
=VLOOKUP(A254,'Tables Removed'!A1:A583,1)

As well as a myriad of other ranges. What is the malfunction here? I do not know why soem ranges are 'broken.' I suppose it is some obvious mistake I am making.

Any help appreciated
 
I googled my question as to ways to remove N/A from the returns for a series of cells including some Vlookup, lookup and simple 'if' equations - I tried all of the responses on here and failed miserably (perhaps its, as my Mum used to say "Its the way you hold your mouth".) I'd be happy to attach my spreadsheet (its a .ZIP file) or to mail it to anyone who can help - I am gettig desperate to solve this little conundrum.

Uncle H
 
I googled my question as to ways to remove N/A from the
returns for a series of cells including some Vlookup,
lookup and simple 'if' equations - I tried all of the
responses on here and failed miserably [....]
I'd be happy to attach my spreadsheet (its a .ZIP file)
or to mail it to anyone who can help - I am gettig
desperate to solve this little conundrum.
Click to expand...

If you are really desperate for a response, try starting a new thread
instead of tacking your question onto a 3-year-old thread that many
people might not see, much less pay attention to.

As for providing an Excel file that demonstrates your problem....

First, try to reduce the problem to its minimum. Remove extraneous
data and formulas that do not affect the problem.

Then upload your Excel file to a file-sharing website. The following
is a list of free file-sharing websites that others have suggested. I
like box.net myself.

MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
http://www.box.net/files

Post the URL of the uploaded file into a response in your (new)
thread.

Be sure to provide pointers to the formula that is causing problems.
 
If you are really desperate for a response, try starting
a new thread instead of tacking your question onto a
3-year-old thread that many people might not see, much
less pay attention to.
Click to expand...

I should have included the following additional suggestion....

For broader participation, you might want to post future inquiries
using the MS Answers Forums at http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
 
What do you mean by "remove"?

N/A is raised when a match cannot be found.

Do you think you should have matches that are not being returned or do you just
want to hide the N/A?

To hide use ISNA function.

=IF(ISNA(VLOOKUP(G1,$A$1:$F$31,2,FALSE)),"",VLOOKUP(G1,$A$1:$F$31,2,FALSE))

If some other issue is bothering you, please describe in more detail.

DO NOT attach any files. please.

If you feel a need to make your file available for download, use one of the file
services like

http://www.savefile.com/

http://freefilehosting.net/

Once you have uploaded, post the URL and someone will have a look.

Be sure you describe your proplem more fully than you have.


Gord Dibben MS Excel MVP
 
Back
Top