+ Reply to Thread
Results 1 to 7 of 7

Vlookup doesn't give result as expected

  1. #1
    Registered User
    Join Date
    06-19-2011
    Location
    Lagos
    MS-Off Ver
    Excel 2007
    Posts
    5

    Vlookup doesn't give result as expected

    Dear Excel Forum,

    Please find attached two excel sheets.
    They have similar data in file Sheet 1 and file Sheet 2.
    There are some spaces which I used Trim Formula to remove.
    Checked the length of the text strings and found them exactly same.
    Yet in Sheet 1 the Vlookup did not pick up the item codes from Sheet 2

    Kindly Assist.

    Thank You to all in Advance.

    Subash S D
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,929

    Re: Vlookup doesn't give result as expected

    None of the strings in Sheet1 are an exact match for the strings in Sheet2. Sheet1 has character code 160 in several places, which look like blanks but they are not. For example

    Please Login or Register  to view this content.
    everything that looks like a space is actually character 160, which is a non-breaking space. It will not match a space (code 32).


    You just need to clean up your data.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,343

    Re: Vlookup doesn't give result as expected

    This will work for some of the data, but not all of it.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    There are still inconsistencies in both files. Leading or lack of spaces (char(32)) or non-breaking spaces (char(160)) muddy the waters and I have struggled to cater for all of the issues.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    06-19-2011
    Location
    Lagos
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Vlookup doesn't give result as expected

    Thank You,

    Can you guide, how to remove the character 160 and replace with simple space.

    Thanks again in advance.

    Subash S D

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,512

    Re: Vlookup doesn't give result as expected

    Find and Replace on the Ribbon.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Registered User
    Join Date
    06-19-2011
    Location
    Lagos
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Vlookup doesn't give result as expected

    Thank You,

    Yes, it did work for all in the list except the first one.

    I shall try on main data now and see how it works.

    Thanks once again please.

    Quote Originally Posted by TMS View Post
    This will work for some of the data, but not all of it.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    There are still inconsistencies in both files. Leading or lack of spaces (char(32)) or non-breaking spaces (char(160)) muddy the waters and I have struggled to cater for all of the issues.
    Thank You,

    Yes, it did work for all in the list except the first one.

    I shall try on main data now and see how it works.

    Thanks once again please.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,106

    Re: Vlookup doesn't give result as expected

    What a mess!! Poor you. try this, which works for all the examples in your sheet:


    =INDEX([sheet2.xlsx]Sheet1!$B$4:$B$11,MATCH(TRIM(SUBSTITUTE(A3,CHAR(160)," ")),TRIM(SUBSTITUTE([sheet2.xlsx]Sheet1!$A$4:$A$11,CHAR(9),"")),0))
    Attached Files Attached Files
    Glenn



+ 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. =AND() not working as expected. Two true functions don't give true result
    By rectorsquid in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-30-2018, 04:34 PM
  2. Replies: 2
    Last Post: 10-05-2017, 10:45 AM
  3. Replies: 4
    Last Post: 06-21-2017, 10:33 AM
  4. INDEX formula trouble, doesn't show expected result
    By Tacita in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2015, 12:33 PM
  5. [SOLVED] Vlook up doesn't give correct result
    By Fabienne88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-15-2013, 10:49 AM
  6. Trimmed mean doesn't give expected result
    By Winton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-03-2012, 06:31 AM
  7. Recorded macro doesn't seem to provide expected result
    By Jeroen1000 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-21-2009, 06:52 AM

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