help - problem with data using vlookup in Excel 2003

I'm using Excel 2003.  I've got two worksheets of data in my workbook,
one copied from a text file, the other downloaded from the Bureau of
the Census, originally in .csv format and saved as an .xls file.

I'm using vlookup to use the state name in one sheet to lookup the data
value I want from the other worksheet.  Vlookup returns the value of
the last cell in the table rather than the value that should be
returned.

I've done some simple testing on the lookup value relative to that in
the table array.  Both entries are the same length.  Both are text
(istext).  Match returns false.

Any ideas as to what is causing my problem (the mismatch) and how to
fix it?

TIA,

John
0
JLH
11/24/2009 3:10:10 AM
excel 39879 articles. 2 followers. Follow

1 Replies
811 Views

Similar Articles

[PageSpeed] 5

One entry may have unseen characters like char 32 spaces and the other data 
may have unseen characters like char 160 spaces.

A1 = Texas<char 32>
D10 = Texas<char 160>

Both have the same length but they still won't match.

Test the very first and last characters of each entry.

=CODE(LEFT(UPPER(A1))
=CODE(RIGHT(UPPER(A1)))

=CODE(LEFT(UPPER(D10)))
=CODE(RIGHT(UPPER(D10)))

If the state name was Texas then the results of those formulas should be:

=CODE(LEFT(UPPER(A1)) = 84
=CODE(RIGHT(UPPER(A1))) = 83

=CODE(LEFT(UPPER(D10))) = 84
=CODE(RIGHT(UPPER(D10))) = 83

If you get any results of 32 or 160 then you have those unseen whitespace 
characters which are (fairly) common when importing/copying/pasting data 
into a sheet.

There is a macro at this site that will remove all those whitespace 
characters from your data. I use this all day long!

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

-- 
Biff
Microsoft Excel MVP


"JLH" <my_name_is_my_own@invalid.invalid> wrote in message 
news:%23c8IjOLbKHA.5544@TK2MSFTNGP02.phx.gbl...
> I'm using Excel 2003.  I've got two worksheets of data in my workbook,
> one copied from a text file, the other downloaded from the Bureau of
> the Census, originally in .csv format and saved as an .xls file.
>
> I'm using vlookup to use the state name in one sheet to lookup the data
> value I want from the other worksheet.  Vlookup returns the value of
> the last cell in the table rather than the value that should be
> returned.
>
> I've done some simple testing on the lookup value relative to that in
> the table array.  Both entries are the same length.  Both are text
> (istext).  Match returns false.
>
> Any ideas as to what is causing my problem (the mismatch) and how to
> fix it?
>
> TIA,
>
> John 


0
T
11/24/2009 3:46:51 AM
Reply:

Similar Artilces:

Help with Mailing Labels
New to Excel and having problems with mailing mailing labels from my list. Any help out there. Jeff Jeff Are you attempting to make labels directly in Excel? It could be done in Excel only, but........ Investigate the use of Word's mailmerge feature to get Excel data to labels and envelopes. Doing it strictly in Excel is difficult and involves much trial and error formatting. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm Gord Dibben Excel MVP On Tue, ...

Access 2003 & the Database Documenter
I want to print out some TableDefs as a reference as I work on myapplication code. But I can't find that thing that used to be called the"Database Documenter".Did they take it out of Access 2003 or did they hide it in some differentplace that I am not seeing?Help?-- Peace & happy computing,Mike Labosh, MCSD MCTOwner, vbSensei.Com"Escriba coda ergo sum." -- vbSensei > I want to print out some TableDefs as a reference as I work on my> application code. But I can't find that thing that used to be called the> "Database Documenter".Nevermind, I f...

a if calculation help
Hi all i have a if calculation which i need to expand but I can't figure it out =IF(AND(D21>=1,D21<=7),"week",IF(AND(D21>=8,D21<=15),"2 wks","3 wks")) I need to expand it if you can help would be much appriciated 1-7 Wk 8 - 15 2 wks 16 - 23 3 wks 24 - 31 1 month 32 - 89 2-3 months 90 - 179 6 months 180 + season I hope you understand that for the numbers i need the result showing in the cell i.e. 1- 7 number showing in cell will answer as week Thankyou for your assistance in advance 360 + season Create a 2 colu...

Strange(?) OWA login problems
Sorry for this rather long message - but I feel lost. I have for a long time had the problem that some of my users can not use OWA to log on to the Exchange server. First one user could not logon, then the next until 4 had permanent problems. The logs showed virtually nothing. (The tests below are made on the internal network to make sure there are no routing or firewall issues. I have Win2k (SP4), Ex2k (SP?), IIS5. The server is the domain controller. IIS has 5 domains (none is the 2000 domain). The test were run from an XP Pro with user A logged in to the domain) Today I decided to take a...

Show only specific Series in the data table
Any way to show series A - D in the graph and not on the data table and series E-H on the data table and not on the graph? The second part is easy: format the data series to have no Line and no Makers I know of no way to achieve the first part best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters REMEMBER: Microsoft is closing the newsgroups; We will all meet again at http://answers.microsoft.com/en-us/office/default.aspx#tab=4 "Legends" <Legends@discussions.microsoft.com> wrote in message ne...

Help! Can't open my Outlook 2003
Message I get is: Cannot start Microsoft Office Outlook. Unable to open the Outlook Window. The set of folders could not be opened. The server is not available. Contact your administrator if this condition persists. Need suggestion on what to do please. What type of email account do you use? Did it work before? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outloo...

How to handle all unhandled exceptions when using Task Parallel Library?
I'm using the Task Parallel Library (TPL) in .NET 4.0. I want to centralize the handling logic of all unhandled exceptions by using the Thread.GetDomain().UnhandledException event. However, in my application, the event is never fired for threads started with TPL code, e.g. Task.Factory.StartNew(...). The event is indeed fired if I use the traditional Thread(threadStart).Start(). This MSDN article (http://msdn.microsoft.com/en-us/library/dd997415%28v=VS.100%29.aspx) suggests to use Task#Wait() to catch the AggregateException when working with TPL, but that is not I want b...

help with outlook 2002 XP
I deleted an old exchange server account as it always showed an error when send/receiving. When I did this I lost all inboz and calendar -- can I retrieve any of this data??? Also cannot even send emails as message pops up saying no data files available. ANy thoughts? ...

Outlook 2000 (Win2K Pro) connection problems
Hi Everyone, I have a few workstations that cannot connect to an Exchange 2000 server from Outlook 2000 (Win2k Pro). Access is still available via OWA and everything was working as of this past Friday. I receive a "The logon credentials supplied were incorrect. Make sure your username and domain are correct, then type your password again." error message when the correct credentials are supplied and tried to use this http://support.microsoft.com/kb/321652/en-us KB with no success. On my XP Pro workstation with Office 2000, I am able to connect as normal. So far I have tried, re...

Access 2000 to 2003 Conversion
Hi, We are about to move to 2003 from 2000. While the Access 2000 file format is compatible with 2003 I wanted to find out if there are reasons we should try and convert all our databases to 2003? Are we going to have any issues if we leave them in the 2000 format and just use them in 2003? Thanks, -- Jonathan Thomas Hampshire County Council The 2002/2003 format is supposed to be faster and more stable than the 2000 format. Microsoft's recommendation is that if everyone is using Access 2002 or 2003 you should use the new format. However, if the 2000 format is working satisfacto...

Hi I'm New and need help with Excel Basics
Please can anyone help me answer any of these questions: How would you add the contents of the cells from A1 to A10. Give 2 ways to do this. (hint: Look up "Examples of common formulas" in Excel help How do you format a cell to have dollar signs next to the numbers? How do you add the contents of cells A1 through A10 on sheet 1 and make the answer show up on sheet 2? How do you rename a sheet? Can I delete the sheets that I don't need? If so how? How do I freeze rows or columns of the sheet so that they don't' move when you scroll up or down? Example: My n...

Entering data
Please help me enter data into cells that seem to be locked. In article <15a201c43e09$dbea4e10$3501280a@phx.gbl>, "Alicia Contreras" <anonymous@discussions.microsoft.com> wrote: > Please help me enter data into cells that seem to be > locked. Hi Alicia, try: Tools > Protect > Unprotect Hope this helps! ...

Create Pareto Chart in Excel? #2
Thanks for the responses. The data is in a sorted frequenc distribution. I have the data analysis add-in installed, but i couldn' locate the data analysis pull down or the histogram with pareto option I'm running excel 2000, I can still create a pareto with this versio correct -- Tycon2 ----------------------------------------------------------------------- Tycon22's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1626 View this thread: http://www.excelforum.com/showthread.php?threadid=27698 The bottom entry on the Tools menu should be "Data Analysi...

Using Drag and Drop for non Adjacent Cells
Hi, I have an Excel work book which has a row of totals in cells which are 6 columns apart. Eg the 1st cell is K71, the next Q71 the next W71etc etc up to a maximum of 25 cells. In another sheet within the same book I have a totals sheet which I need to display the totals from the 1st sheet. I have copied the 1st 2 references from the original sheet into cells which are adjacent to each other in my totals sheet with the intention of dragging and dropping for the next 23 or so cells hoping that Excel would pick up on the fact that the referenced cells are 6 rows apart. But this does not ha...

Vlookup with wildcard
I want to set up a list with certain zip codes but do not want to put them all in. Is there a way I can use wildcards? When the zip code is found, it will put "local" in the cell. example: zip code code 10300 Local 10301 Local 10302 Local ....... ....... ...

Excel files won't come up
I am having trouble bringing up any Excel file. I get an error message with a error signature like this AppName:Excel.exe AppVer 9.0.0.2719 ModName:mso9:11 ModVer:9.0.0.2720 Offset:00045909 When I look up the tech information at the bottom I get this c:\docume~1\HarryG~1\Locals~1\temp\2958_appcompat.txt I sent this message last week and the remark was to double click and copy the error and reply. I did and I haven't seen any reply yet. I really need to use the Excel files which I use often. All other ofice files OK Help!!! I don't have a guess about what's ...

Adding button using wizard in Access 07
Hi, I'm trying to add a simple button to advance the record page to a new blank record using the wizard. Here's what I've done: Created the form which works fine. I've added records to test it. In Design view I click on Use Controls Wizards to activate it. Click on Button to select it. Click on the form to place the button and activate wizard. This does happen. In the wizard I elect Record Operations under Categories and then Add New Record for the Action. Click Next and then select Text to identify the button. Click Next again and change name to Add Record. Clic...

Missing contacts Exchange 2003
Weid, there are no contacts in the main Contacts folder, is there a way to import them from AD to Exchange 2003? Thanks AD contacts show up in the GAL. Each user has their own private contacts folder. -GT "Tomek" <tomaszch@yahoo.com> wrote in message news:7eda0bae.0409180906.1c7c6ce2@posting.google.com... > Weid, there are no contacts in the main Contacts folder, is there a > way to import them from AD to Exchange 2003? > > Thanks ...

Format Data Entry
Is there a way to format data so that when entering someone's height as 511 it will come up as 5'11" and when entering 63 it would come up as 6'3" ? Thanks. There may be a way, but I wouldn't do this. Instead, use two cells (feet and inches). Or use one cell and enter total inches. It'll make any further arithmetic much, much easier. Ayuda wrote: > > Is there a way to format data so that when entering someone's height as 511 > it will come up as 5'11" and when entering 63 it would come ...

New in Money'04: Online Services Valid Two or Three Years from first use
From the new Money 2004 pages: http://www.microsoft.com/money/info/comparison.asp "Online services valid two or three years from first use. Online bill pay, downloading transactions from your financial institution, downloading stock quotes, and the ability to synchronize with MSN� Money Plus or MSN Money expire two years from first use of Microsoft Money Standard or September 1, 2006, whichever is earlier. Online bill pay, downloading transactions from your financial institution, downloading stock quotes, and the ability to synchronize with MSN� Money Plus or MSN Money expire three year...

Simple problem
I am doing something obviously stupid but can't see what In a spreadsheet I have data in cols A and B In cell B1 I have =(a1-0.375) I have copied this to b2.b14 with the result shown: 1 0.625 2 0.625 3 0.625 4 0.625 5 0.625 6 0.625 7 0.625 8 0.625 9 0.625 10 0.625 11 0.625 12 0.625 13 0.625 14 0.625 If I go to cell b2 and then left click in the formula bar then hit enter, b2 evaluates to 1.625. Same with b3, b4 etc. What have I done to stop the formula evaluating correctly after I have copied it? Thanks...

"Unable to display folder" accessing additional mailboxes in Outlook 2003 as administrator
Article 262399 properly describes the process, however for a new machine with Windows 2003 Server, Exchange 2003 and Outlook 2003 (on console), this right appears to be already granted to all mailboxes. That is "domain"\administrator already has full mailbox access although it appears greyed out on purpose. Deleting and re-adding is not allowed. So, if one logs onto the console as an administrator and adds the additional mailboxes to the exchange server profile, one gets the typical "unable to display folder" when clicking on the mailbox in Outlook 2003. Even though th...

Can't save new Excel Doc
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Help! I have created an excel spreadsheet (with multiple sheets in one doc) but can't save the document. I keep getting an error message that either: <br> - file name or path name does not exist <br> - file is being used by another program <br> - name is the same as another file <br><br>Can anyone tell me why I can't save. (To clarify, none of the &quot;reasons&quot; it won't save are applicable as I have double checked each of those). <br><br>Thanks, <br> Moll...

Form format changed from 2003 to 2007
I have import an application I developed in Access 2003 to Access 2007. All of my forms are now showing up full screen with a tab. Even the small forms, such as the CopyRight, which is supposed to be a small 2” by 3” window. It now takes up the full screen with a tab and my 2” x 3” message on a full screen white background. This is true with all my forms. Is wish to keep the old look and fell until I have time to migrate the forms over. Right now, they look stupid in 2007. Is there some simple property or config setting that will allow my forms to be windows at the size I had ...

How to import MS Access data into MS CRM 02-28-07
Hello all, My questions is sample. it 's possible import contact with data SQL into CRM. If yes How do ? Thanks for answers David David, It is possible to import data (from Access OR SQL) into CRM. Our Company uses a product called Scribe to accomplish this (www.scribesoft.com). From what I understand, this is the only 'supported' way to import data into CRM entities. You would basically connect the source data through one adapter (either for Access or SQL) and through a CRM Adapter into CRM. I hope that this answers your question. Thanks, John Thibodeau www.ibisin...