Vlookup returning #N/A when it should not
robs3131
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!
--
Robert
Pete_UK
describe your data layout - your post implies an earlier thread, but I don't
remember seeing it.
Pete
"robs3131" <robs...@discussions.microsoft.com> wrote in message
news:79EAFE70-588C-4F87...@microsoft.com...
mike in texas
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
Marc
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...
"robs3131" <robs...@discussions.microsoft.com> wrote in message
news:79EAFE70-588C-4F87...@microsoft.com...
robs3131
(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
--
Robert
Pete_UK
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
"robs3131" <robs...@discussions.microsoft.com> wrote in message
news:A0DF9446-BE20-4CB6...@microsoft.com...
Tyro
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
"robs3131" <robs...@discussions.microsoft.com> wrote in message
news:79EAFE70-588C-4F87...@microsoft.com...
robs3131
So frustrating that it just comes down to something so simple after so many
hours spent looking into this... :)
--
Robert
Pete_UK
Sometimes the most frustrating problems have an easy solution.
Pete
On Mar 1, 1:52 am, robs3131 <robs3...@discussions.microsoft.com>
wrote:
> 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... :)
> --
> Robert
>
>
>
> "Pete_UK" wrote:
> > 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
>
> > "robs3131" <robs3...@discussions.microsoft.com> wrote in message
> > >> > Robert- Hide quoted text -
>
> - Show quoted text -
holly
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...
Niek Otten
If the 4th argument of the VLOOKUP is omitted or TRUE, the list has to be
sorted ascending
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"holly" <ho...@discussions.microsoft.com> wrote in message
news:285CCEBC-D727-44B2...@microsoft.com...
Tz -
=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
> On Friday, February 29, 2008 9:23 AM robs313 wrote:
> 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!
> --
> Robert
>> On Friday, February 29, 2008 10:26 AM Pete_UK wrote:
>> 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 do not
>> remember seeing it.
>>
>> Pete
>>> On Friday, February 29, 2008 11:29 AM mikeintexa wrote:
>>> 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
>>>
>>>
>>> "robs3131" wrote:
>>>> On Friday, February 29, 2008 4:19 PM Marc wrote:
>>>> 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...
>>>>
>>>>
>>>> "robs3131" <robs...@discussions.microsoft.com> wrote in message
>>>> news:79EAFE70-588C-4F87...@microsoft.com...
>>>>>> On Friday, February 29, 2008 7:38 PM Pete_UK wrote:
>>>>>> 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
>>>>>>
>>>>>>
>>>>>> "robs3131" <robs...@discussions.microsoft.com> wrote in message
>>>>>> news:A0DF9446-BE20-4CB6...@microsoft.com...
>>>>>>> On Friday, February 29, 2008 7:50 PM Tyro wrote:
>>>>>>> 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
>>>>>>>
>>>>>>> "robs3131" <robs...@discussions.microsoft.com> wrote in message
>>>>>>> news:79EAFE70-588C-4F87...@microsoft.com...
>>>>>>>> On Friday, February 29, 2008 8:52 PM robs313 wrote:
>>>>>>>> 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... :)
>>>>>>>> --
>>>>>>>> Robert
>>>>>>>>
>>>>>>>>
>>>>>>>> "Pete_UK" wrote:
>>>>>>>>> On Saturday, March 01, 2008 5:11 PM Pete_UK wrote:
>>>>>>>>> Glad to hear it worked for you.
>>>>>>>>>
>>>>>>>>> Sometimes the most frustrating problems have an easy solution.
>>>>>>>>>
>>>>>>>>> Pete
>>>>>>>>>
>>>>>>>>> On Mar 1, 1:52=A0am, robs3131 <robs3...@discussions.microsoft.com>
>>>>>>>>> wrote:
>>>>>>>>> ed. =A0
>>>>>>>>> y
>>>>>>>>> Ps
>>>>>>>>> s:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> in
>>>>>>>>> mat"
>>>>>>>>> as
>>>>>>>>> hat
>>>>>>>>> e
>>>>>>>>> trying
>>>>>>>>> es
>>>>>>>>> ")
>>>>>>>>> ")
>>>>>>>>> ")
>>>>>>>>> ")
>>>>>>>>> es
>>>>>>>>> re
>>>>>>>>>
>>>>>>>>> NA
>>>>>>>>> were
>>>>>>>>> tion to
>>>>>>>>> as
>>>>>>>>> ormats
>>>>>>>>> to be
>>>>>>>>>
>>>>>>>>> that
>>>>>>>>>> On Tuesday, November 10, 2009 12:23 PM holly wrote:
>>>>>>>>>> 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...
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> "Tyro" wrote:
>>>>>>>>>>> On Tuesday, November 10, 2009 12:32 PM Niek Otten wrote:
>>>>>>>>>>> Always post your formula
>>>>>>>>>>> If the 4th argument of the VLOOKUP is omitted or TRUE, the list has to be
>>>>>>>>>>> sorted ascending
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> Kind regards,
>>>>>>>>>>>
>>>>>>>>>>> Niek Otten
>>>>>>>>>>> Microsoft MVP - Excel
>>>>>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>>>>>> AJAX Web Service Driven Customers Table With Customer Details
>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/4c3d2726-d99e-4f83-9e49-0d4867b6271a/ajax-web-service-driven-customers-table-with-customer-details.aspx
Tz -
Sorry for the wasted time~
Frank Tunney
Uncle H
>>>>>>>>>>>> On Thursday, August 19, 2010 8:31 PM Tz - wrote:
>>>>>>>>>>>> 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
>>>>>>>>>>>>> On Thursday, August 19, 2010 8:34 PM Tz - wrote:
>>>>>>>>>>>>> I did not have it sorted ascending. This is what I get for playing with excel when tired.
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> Sorry for the wasted time~
>>>>>>>>>>>>> Submitted via EggHeadCafe
>>>>>>>>>>>>> ASP.NET In-Memory Image Control with Built-In Resizing of Posted File
>>>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/e1a14e2c-e746-4bed-a552-24c632bd2709/aspnet-inmemory-image-control-with-builtin-resizing-of-posted-file.aspx
joeu2004
> 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.
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.
joeu2004
> 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.
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.
Gord Dibben
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
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