+ Reply to Thread
Results 1 to 14 of 14

VLOOKUP text values

  1. #1
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    VLOOKUP text values

    I have a worksheet filled with GPS data (84k lines). From another page, I used VLOOKUP to find the location. The problem I am having is that I have no clue how Excel is formatting the data. Both time columns are formatted as TEXT. I do this so it will sort my data correctly even if there are leading zeros or odd seconds (e.g. 0944, 095230, 1000, 1005 would be sorted correctly).

    In 151 samples, Excel found exactly one match from the GPS table. I am guessing that there is some "correct" format in that cell, though do not know why it would be different than the rest of the column (all entered manually).

    If I paste a time from the GPS page, it does the VLOOKUP correctly with ostensibly the exact same text string "164345." Both pages show these as being formatted as "Text," I can find no spaces or anything that confuse the comparison.

    Formula: =VLOOKUP(AA4,GPS_data,4,0) Return : #N/A Even when a match is obviously present in the GPS_data array.

    How can I find out what Excel sees when it is trying to read this cell, AA4? And why it does't match the exact same string in the lookup array most of the time?

    Thanks for any insights.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: VLOOKUP text values

    If it is stored as text, it most likely has an apostrophe before the number. However, the apostrophe will not be visible in the cell, but it is there. So the number 0944 is actually '0944 in the cell.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: VLOOKUP text values

    Any clue how to make these two columns match in format, so that we can see if they are they same? I have tried format painter (looks like wasted space on the ribbon). The strings from the GPS were pasted from the data logger file into Excel.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: VLOOKUP text values

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data. (Remove private or sensitive data that shouldn't be published)

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Click on Browse under Upload Files from your Computer and then find the file on your computer using the dialogue that opens. Click on Open then click on Upload. Click on Close this Window. Click on Submit.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: VLOOKUP text values

    Just messed things up even more. I tried concatenating ' & cell value and pasting those in and now nothing works. I cannot even seem to go back to the original formats.

  6. #6
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: VLOOKUP text values

    Not sure if I can attach a workbook. I know my system will not allow me to download one. Hopefully this will not be stripped away.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: VLOOKUP text values

    In the attached file, I have a GPS tab with times in column C. On Sheet 1, there are time lookup values for latitude and longitude. One of the three seems to work. The other two are there, but do not like the format. Oddly, the 164645 that works here did not work in the larger dataset. This is probably something obvious, but I am not seeing it. Thanks for your help.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,233

    Re: VLOOKUP text values

    I am seeing a lot of confusion around numbers and text and when is a number a number and when is it text.

    In sheet1, I put =ISTEXT(C2) into F2 and copied down. This shows me that C2 and C4 are numbers stored as text, but C3 is a real number. Similarly, I put =ISTEXT(C2) into GPS!J2 and copied down, and got a column of FALSE, indicating that these are also real numbers. The failed lookups based on C2 and C4 fail because they are trying to compare numbers stored as text to real numbers. The lookup on C3 works because C3 is a real number. I would expect that your lookups will work just fine if you choose which datatype (text or number) you want to use and make certain that all of the values are of the appropriate data type.

    Your posts have suggested that you have tried to keep all of these as text strings, so I was a little surprised that they were numbers. You will probably have to think more carefully through the processes you are using to keep them as text, because they are clearly getting converted to numbers somewhere along the way.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: VLOOKUP text values

    Your data seems to bee a mixture of text and numbers. I selected column C of the GPS worksheet and then selected GENERAL as the format.
    On Sheet1 the values were text values. I selected the column and clicked on the yellow diamond that appeared and selected to convert the values to numbers. All worked correctly after that.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: VLOOKUP text values

    I don't get a "yellow diamond" appearing at all. Where does this appear and what is it? It is odd that the Sheet1 values were formatted differently as they were manually typed into a column also pre-defined as "Text." This column cannot be converted to numbers or it will not be able to be sorted correctly as I stated in the original post.

    The GPS values must be being formatted as numbers when they are pasted from the NMEA text strings, even though I predefined the column as "Text." Is there a way to convert the GPS page column to Text? When I select the format, it does not seem to change anything.

  11. #11
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: VLOOKUP text values

    OK. Excel still has me scratching my head. Seems like the format bar selection is never really applied. I used an =TEXT(C2,"000000") and then pasted those as values back into the GPS time column. Seems to be working now. Thanks. I will mark this as solved. (Arrgh..if I can remember how to do that)

    JD

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: VLOOKUP text values

    On shrrt1, select column C. Click on the DATA tab Text to Columns. Click OK until there is a choice to define the column contents...choose TEXT click finish.

    Repeat procedure on GPS column C. All values should now be text and the formula should work

  13. #13
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: VLOOKUP text values

    Thanks, I did not know that existed on the Data tab. Looks like an easier way than the =TEXT() function and repasting values, but that worked, too. The format selection on the Home tab does not seem to give me what I would expect.

  14. #14
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: VLOOKUP text values

    The format selection on the Home tab has definite limitations and you have found one of them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Vlookup for text and add relevant values if text matches in the range
    By misys.til in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-19-2014, 06:11 AM
  2. Replies: 12
    Last Post: 01-10-2008, 09:34 AM
  3. Vlookup text values
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-06-2005, 06:05 PM
  4. [SOLVED] Vlookup text values
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 11:05 AM
  5. Vlookup text values
    By marin_michael@yahoo.ca in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. Vlookup text values
    By marin_michael@yahoo.ca in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] Vlookup text values
    By marin_michael@yahoo.ca in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1