vlookup problem #10

This one really blows me away!

I just reinstalled ExcelXP and created a worksheet to determine a state 
value (D) from an area code(C) with the following columns:
      A        B                        C   D
      #N/A 405 201  NJ
      #N/A 800 202   DC
      #N/A 620 203  CT
      #N/A 800 204  MB
      #N/A 888 205  AL
      #N/A 303 206  WA
      #N/A 734 207  ME
      #N/A 800 208  ID
      #N/A 806 209  CA
      #N/A 816 210  TX
      #N/A 770 212  NY
      #N/A 610 213  CA
      #N/A 404 214  TX
      #N/A 314 215  PA
      #N/A 501 216  OH
      #N/A 404 217  IL
      #N/A 336 218  MN
      #N/A 207 219   IN
      #N/A 203 224  IL
      #N/A 877 225   LA
      #N/A 608 228  MS
      #N/A 905 229  GA

....to 350 rows

The formula in Column A is =vlookup(B1,$C$1:$D$350,2)
Everything is formatted as General (I tried number & Text as well)
It looks like something is wrong with the formula or all the data, but in 
fact, 3 values work.

I've never seen this before. Doe you have any ideas on what might be wrong?

Thanks,
Jim


0
jazzzbo (19)
9/28/2005 3:57:39 PM
excel 39879 articles. 2 followers. Follow

4 Replies
574 Views

Similar Articles

[PageSpeed] 43

Jim, copy a blank cell. Then select all your #N/As and hit Edit-->Paste 
special-->Add. See if that forces Excel to see your column A data as 
numbers. If that doesn't work, you might want to do it on B and/or C as 
well.
************
Anne Troy
www.OfficeArticles.com

"Jim Berglund" <jazzzbo@shaw.ca> wrote in message 
news:T3z_e.6670$tl2.5577@pd7tw3no...
> This one really blows me away!
>
> I just reinstalled ExcelXP and created a worksheet to determine a state 
> value (D) from an area code(C) with the following columns:
>      A        B                        C   D
>      #N/A 405 201  NJ
>      #N/A 800 202   DC
>      #N/A 620 203  CT
>      #N/A 800 204  MB
>      #N/A 888 205  AL
>      #N/A 303 206  WA
>      #N/A 734 207  ME
>      #N/A 800 208  ID
>      #N/A 806 209  CA
>      #N/A 816 210  TX
>      #N/A 770 212  NY
>      #N/A 610 213  CA
>      #N/A 404 214  TX
>      #N/A 314 215  PA
>      #N/A 501 216  OH
>      #N/A 404 217  IL
>      #N/A 336 218  MN
>      #N/A 207 219   IN
>      #N/A 203 224  IL
>      #N/A 877 225   LA
>      #N/A 608 228  MS
>      #N/A 905 229  GA
>
> ...to 350 rows
>
> The formula in Column A is =vlookup(B1,$C$1:$D$350,2)
> Everything is formatted as General (I tried number & Text as well)
> It looks like something is wrong with the formula or all the data, but in 
> fact, 3 values work.
>
> I've never seen this before. Doe you have any ideas on what might be 
> wrong?
>
> Thanks,
> Jim
>
> 


0
ng1 (1444)
9/28/2005 4:01:19 PM
Nope! No joy!
I did get one cell to change from #NA to #VALUE, but that's all.

Any other suggestions?

Jim


"Anne Troy" <ng@officearticles.com> wrote in message 
news:e7KSfXExFHA.1028@TK2MSFTNGP12.phx.gbl...
> Jim, copy a blank cell. Then select all your #N/As and hit Edit-->Paste 
> special-->Add. See if that forces Excel to see your column A data as 
> numbers. If that doesn't work, you might want to do it on B and/or C as 
> well.
> ************
> Anne Troy
> www.OfficeArticles.com
>
> "Jim Berglund" <jazzzbo@shaw.ca> wrote in message 
> news:T3z_e.6670$tl2.5577@pd7tw3no...
>> This one really blows me away!
>>
>> I just reinstalled ExcelXP and created a worksheet to determine a state 
>> value (D) from an area code(C) with the following columns:
>>      A        B                        C   D
>>      #N/A 405 201  NJ
>>      #N/A 800 202   DC
>>      #N/A 620 203  CT
>>      #N/A 800 204  MB
>>      #N/A 888 205  AL
>>      #N/A 303 206  WA
>>      #N/A 734 207  ME
>>      #N/A 800 208  ID
>>      #N/A 806 209  CA
>>      #N/A 816 210  TX
>>      #N/A 770 212  NY
>>      #N/A 610 213  CA
>>      #N/A 404 214  TX
>>      #N/A 314 215  PA
>>      #N/A 501 216  OH
>>      #N/A 404 217  IL
>>      #N/A 336 218  MN
>>      #N/A 207 219   IN
>>      #N/A 203 224  IL
>>      #N/A 877 225   LA
>>      #N/A 608 228  MS
>>      #N/A 905 229  GA
>>
>> ...to 350 rows
>>
>> The formula in Column A is =vlookup(B1,$C$1:$D$350,2)
>> Everything is formatted as General (I tried number & Text as well)
>> It looks like something is wrong with the formula or all the data, but in 
>> fact, 3 values work.
>>
>> I've never seen this before. Doe you have any ideas on what might be 
>> wrong?
>>
>> Thanks,
>> Jim
>>
>>
>
> 


0
jazzzbo (19)
9/28/2005 4:33:23 PM
Some more things to check...

First, I would think that your formula would be more like:

=vlookup(B1,$C$1:$D$350,2,false)

(You want an exact match)

If you can pick out the cell that contains the "match" in column C, you could
double check to see if the values are the same:

=b1=c###

If it comes back false, then there's a difference.  Either number vs. text (like
Anne suggested) or maybe leading/trailing spaces???

Jim Berglund wrote:
> 
> This one really blows me away!
> 
> I just reinstalled ExcelXP and created a worksheet to determine a state
> value (D) from an area code(C) with the following columns:
>       A        B                        C   D
>       #N/A 405 201  NJ
>       #N/A 800 202   DC
>       #N/A 620 203  CT
>       #N/A 800 204  MB
>       #N/A 888 205  AL
>       #N/A 303 206  WA
>       #N/A 734 207  ME
>       #N/A 800 208  ID
>       #N/A 806 209  CA
>       #N/A 816 210  TX
>       #N/A 770 212  NY
>       #N/A 610 213  CA
>       #N/A 404 214  TX
>       #N/A 314 215  PA
>       #N/A 501 216  OH
>       #N/A 404 217  IL
>       #N/A 336 218  MN
>       #N/A 207 219   IN
>       #N/A 203 224  IL
>       #N/A 877 225   LA
>       #N/A 608 228  MS
>       #N/A 905 229  GA
> 
> ...to 350 rows
> 
> The formula in Column A is =vlookup(B1,$C$1:$D$350,2)
> Everything is formatted as General (I tried number & Text as well)
> It looks like something is wrong with the formula or all the data, but in
> fact, 3 values work.
> 
> I've never seen this before. Doe you have any ideas on what might be wrong?
> 
> Thanks,
> Jim

-- 

Dave Peterson
0
petersod (12005)
9/28/2005 4:39:13 PM
I can't imagine why, Jim. Can you send your file?
************
Anne Troy
www.OfficeArticles.com

"Jim Berglund" <jazzzbo@shaw.ca> wrote in message 
news:nBz_e.6706$tl2.1712@pd7tw3no...
> Nope! No joy!
> I did get one cell to change from #NA to #VALUE, but that's all.
>
> Any other suggestions?
>
> Jim
>
>
> "Anne Troy" <ng@officearticles.com> wrote in message 
> news:e7KSfXExFHA.1028@TK2MSFTNGP12.phx.gbl...
>> Jim, copy a blank cell. Then select all your #N/As and hit Edit-->Paste 
>> special-->Add. See if that forces Excel to see your column A data as 
>> numbers. If that doesn't work, you might want to do it on B and/or C as 
>> well.
>> ************
>> Anne Troy
>> www.OfficeArticles.com
>>
>> "Jim Berglund" <jazzzbo@shaw.ca> wrote in message 
>> news:T3z_e.6670$tl2.5577@pd7tw3no...
>>> This one really blows me away!
>>>
>>> I just reinstalled ExcelXP and created a worksheet to determine a state 
>>> value (D) from an area code(C) with the following columns:
>>>      A        B                        C   D
>>>      #N/A 405 201  NJ
>>>      #N/A 800 202   DC
>>>      #N/A 620 203  CT
>>>      #N/A 800 204  MB
>>>      #N/A 888 205  AL
>>>      #N/A 303 206  WA
>>>      #N/A 734 207  ME
>>>      #N/A 800 208  ID
>>>      #N/A 806 209  CA
>>>      #N/A 816 210  TX
>>>      #N/A 770 212  NY
>>>      #N/A 610 213  CA
>>>      #N/A 404 214  TX
>>>      #N/A 314 215  PA
>>>      #N/A 501 216  OH
>>>      #N/A 404 217  IL
>>>      #N/A 336 218  MN
>>>      #N/A 207 219   IN
>>>      #N/A 203 224  IL
>>>      #N/A 877 225   LA
>>>      #N/A 608 228  MS
>>>      #N/A 905 229  GA
>>>
>>> ...to 350 rows
>>>
>>> The formula in Column A is =vlookup(B1,$C$1:$D$350,2)
>>> Everything is formatted as General (I tried number & Text as well)
>>> It looks like something is wrong with the formula or all the data, but 
>>> in fact, 3 values work.
>>>
>>> I've never seen this before. Doe you have any ideas on what might be 
>>> wrong?
>>>
>>> Thanks,
>>> Jim
>>>
>>>
>>
>>
>
> 


0
ng1 (1444)
9/28/2005 4:45:19 PM
Reply:

Similar Artilces:

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

Problem with CFTPConnection
Hi I've an application that connects to an FTP Server and uploads a big directory (size over 500 MB). Now what happens is that after sometime the application stops uploading files and stays idle. The FTP server connection timeout value is set to 2 minutes. So the server seeing the connection idle for two seconds, disconnect the session and no more file is uploaded. On debugging I found out that after some time CFTPConnection.PutFile() method hangs on some random file and stays like that for 5 minutes (default send timeout value of CFTPSession). During this time the server connection timeo...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

Memory Leak #10
I added to the main MFC dll class sterilization support. In the InitInstance I added the following (and a corresponding code in the ExitInstance). It seems to work fine but I get memory leak on the program shutdown. Can someone advise please? Gilai .... CFile theFile; theFile.Open("Dllpcap.dat", CFile::modeRead); CArchive archive(&theFile, CArchive::load, 4096); Serialize(archive); archive.Close(); theFile.Close(); Do you know what/where the memory leak is happening. There are some things in MFC that just get allocated once and perhaps they just never clea...

How to make Run Integration work in GP 10
Can anyone tell me how to make the Run Integration tool work in GP 10. We just did our first client upgrade to GP 10 and the client has only 1 integration that they used to be able to run via the RUN INTEGRATION Tool. Any help would be greatly appreciated. -- Tammy Chavez I have the same probelm...did you get any answers to this? I have to go into Integration Manager to run it b/c Tools>Run Integration does nothing. I even checked the ini files to make sure the path is correct. "Tammy Chavez" wrote: > Can anyone tell me how to make the Run Integration tool work in...

CRM Outlook Client Configuration Wizard Problem
CRM 4.0 - Microsoft Outlook Client Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client ins...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Microsoft Outlook 03-11-10
I have just got a message saying not installed for the current user and it will not load. But I've been using it for years. George George;131443 Wrote: > I have just got a message saying not installed for the current user and > it > will not load. But I've been using it for years. > > GeorgeGeorge, welcome to the forum. When you post (here or in any forum), you will get faster and less frustrating replies if you do a few standard things: - try to define the problem in the thread title. Since this is an Outlook forum, simply stating "Mic...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Root Business Unit Disabled 10-07-05
I inadvertently disabled the root business unit. Now I cannot log in as the administrator was member of the same unit. How do I re-enable this? go into the database and find the table called businessunitbase - then look at the field called isdisabled. You will probably find this is set to 1. try set it to 0 then see if you can logon. Make sure you backup the database before doing this "ril" <ril@discussions.microsoft.com> wrote in message news:CCC43C2F-F109-49BE-A4AC-287AFE0F4D93@microsoft.com... >I inadvertently disabled the root business unit. Now I cannot log in a...

Excel Jululian 04-24-10
Good morning please help i have a lists of names and i want to separate each in a colum like You Light Up My Life197711 You Light Up My Life (in one colum "A") 1977 (in one colum "B") 1 (in one colum "C") 1 (in one colum "E") regards First many thanks on your reply the text are different in length regards "מיכאל (מיקי) אבידן" wrote: > 'Data' > 'Text to Columns' > 'Next' > mark/un-mark the vertical separators as > shown in the picture > 'Finish' >...

Excel List Sorting Problem (Descending)
Hi there, I'm having trouble sorting my list--my column contains *only* 4-digit numbers but when I click on "descending order", only about the first half of the rows are arranged this way, before it begins again to arrange the rest in descending order. Like this: 5120 5119 5118 4000 3050 5116 4112 etc. Has this problem happened for anybody else? I'd appreciate any help you can offer. Part of your list is text, although it looks like numbers. Format an empty cell as number. Enter the number 1. Copy. Select your "numbers". Edit>Paste Special, check Mul...

VLOOKUP output
Hi. I need to use an if formula If the VLOOKUP formula in the "list" sheet doesn't find any information in the "price" sheet, I want to use an IF formula to get information from another column in the "list" sheet. But if the VLOOKUP formula cant find any information in another sheet but the output is (in swedish) #saknas! (I think it returns #N/A in english) so I cant use the IF formula. VLOOKUP should be ideal for that. Don't use IF. Please post your formula and inputs. Is the lookup table sorted? -- Kind regards, Niek Otten Mi...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Hyperlinks #10
Is there a way in excel to turn off the hyperlink feature for an entire column of data? I have a column of email addresses, and I don't want any of them to be an active hyperlink. I can remove the hyperlink individually, but have not been able to find a way to do it for the entire column of data. Thanks! Candace A small macro will remove any existing hyperlinks. Sub Delete_HLinks() For Each cel In Selection cel.Hyperlinks.Delete Next cel End Sub To prevent future hyperlinks for the entire sheet go to Tools>Autocorrect Options>AutoFormat as you type. Un-select &q...

Printing problem in booklet format
When I try to print my booklet document, on the screen it looks perfect, but when i go into print preview it only has half of each page on the paper. When i print, it comes out exactly like that. I have Publisher 2003 and run it on XP home. My printer is an Epson stylus Photo RX650. I contacted Epson and they say its a Publisher problem. Any suggestions. Driver? http://tech.epson.com.au/downloads/product.asp?sCategory=Multi_Functional&id=stylusphotorx650&FileType=1&EmailAdd=&MetricIDReturned=624915&platform=winxp Are you selecting booklet in page setup? Do you ...

problem opening up program/file
Hello, All. I am having some trouble opening up Word. It was working okay until I loaded some fonts to FontBook. Okay, now I removed all my fonts but I still get this error message about corrupt fonts I have on my computer. When I open Word, I see the blue starting-up window: Word:Mac 2004. Right underneath the product ID #, I see that it is initiating... when it gets to the point "Optimizing font menu performance" I start getting this error windows popping up one after another. (E.g. The font M TimesSmallText is corrupt and should be removed.) I keep on clicking okay for a whole lot...

Money deluxe 2007 password problem
When I update my bank account transactions I get a message saying that my account was not updated because my password is incorrect. I then go into account info and have to update my password. I save the update, but then it happens again sporadically when I open Money at later times. Is there a way to fix this glitch? ...

printing problems with memorized reports
We have recently been running into printing problems with our memorized reports. We will print preview a report and it will look fine on screen, then we will send to the printer, and the report print extremely small in the corner of the page. However if we go back in and re-print the report then it will print normal size on the page. We are not making any settings changes at all, and when it does this appears to just be at random. We are printing to an HP laserjet 4000 series printer that is on our network. We've used RMS with the same configuration for approx. 5 years now and ...

Reports and sub-reports problems
Dear Sirs,I have a report with 4 sub-reports.How can it be that 2 sub-reports are showing the data triple.Can someone help?ThanksKlaus I just realized. It's not due to being a subreport - it's already showing on the normal report triple data. It was not before and I did not change anything. I hope that someone can help me.Thanks Klaus"Amateur" wrote:> Dear Sirs,> I have a report with 4 sub-reports.> How can it be that 2 sub-reports are showing the data triple.> Can someone help?> Thanks> Klaus...

VLookUp Help!
I am trying to put together a pricing wizard for my company that would allow reps and resellers to easily look up the prices of our products. I originally designed a pivot table with drop down lists but this proved to be to difficult for most people to use. I though instead I could use VLookup to accomplish the same thing. The problem is we price our products by quantity. 5 to 25 being one price 26 to 50 being another and so on. Because of the ranges of pricing I can not figure a way, using VLookup to actually look up a price. Here is an example of the pricing: # Part # of Licen...

connection problems
a popup saying internet explorer cannot diaplay webpage and it is a connection problem -- eummer2010 how does your problem/question pertain to windows update? terri wrote: > a popup saying internet explorer cannot diaplay webpage and it is a > connection problem Hello Terry, Would be nice if you could tell us more information, like what OS you're using, Service Pack, what other security software are you running, if any? etc. IE6 ? IE7 ? IE8 ? Repoяt problems and check for solutions automatically <http://windowshelp.microsoft.com/Windows/en-US/Help/...