+ Reply to Thread
Results 1 to 11 of 11

Vlookup function doesn't work on identical text

  1. #1
    Registered User
    Join Date
    11-15-2012
    Location
    Middle East
    MS-Off Ver
    Excel 2010
    Posts
    60

    Vlookup function doesn't work on identical text

    Hi guys,

    I have an example for same text on which vlookup doesn't recognize it in the attached file.

    The vlookup formula is in cell B4. As you can see, it returns #N/A.

    If I try copying cell A1 to A4 or vice versa, the vlooup formula does display the correct value.

    What can be the reason for that?

    Thank you,

    Moty
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Vlookup function doesn't work on identical text

    The value in A4 is a "number stored as text", not a real number.
    But the value in A1 IS a real number.
    So they are not identicle.

    Try copying any blank cell
    Right click on A4
    Paste Special - Values - Add - OK

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Vlookup function doesn't work on identical text

    a1 is a number a4 is text try
    =VLOOKUP(A4+0,$A$1:$B$1,2,0)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Vlookup function doesn't work on identical text

    Enter these formulas in your example:
    D1: =ISNUMBER(A1)
    E1: =ISTEXT(A1)

    D4: =ISNUMBER(A4)
    E4: =ISTEXT(A4)

    You'll see that A1 is numeric and A4 is text.
    You'd need to convert both to either text or numbers.

    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Vlookup function doesn't work on identical text

    Beside that.. we have one more option to convert Numeric Text to Value by. Value function.

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


    Give a try..

  6. #6
    Registered User
    Join Date
    11-15-2012
    Location
    Middle East
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Vlookup function doesn't work on identical text

    Thank you all very much for your answers.

    How come that even after using the "Format Painter" which changes the cells to become with the same format (e.g. 'Number' format), vlookup formula still doesn't work?

    How can I change a 'text' format value to a 'number' format value if I can't use the "Format cells" dialog box for that?


    Thanks again,

    Moty

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Vlookup function doesn't work on identical text

    Applying a format to a cell doesn't alter/modify the value in the cell.
    It only changes the 'appearance' of the value in the cell.

    Observe..
    Type 1.27 in a cell
    Format that cell as a Number with 1 Decimal.
    Now highlight that cell and look at the cell, it shows 1.3
    But look in the formula bar, it still shows 1.27

  8. #8
    Registered User
    Join Date
    11-15-2012
    Location
    Middle East
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Vlookup function doesn't work on identical text

    Thank you Jonmo.

    So how can I change a 'text' format value to a 'number' format value if I can't use the "Format cells" dialog box for that?

  9. #9
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: Vlookup function doesn't work on identical text

    you don't have to change the actual format of a cell and still can reference it in a different format. For example, if cell a2 have a text value of 01, when you reference to a2, you can reference to it as a number by simply add value(a2). I hope it make sense.

  10. #10
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Vlookup function doesn't work on identical text

    Try this:
    • Select a blank cell
    • Right-Click...Select: Copy
    • Select the range of "numbers"
    • Home.Paste.Paste_Special
    ...Check: Add
    ...Click: Finish

    Does that help?

  11. #11
    Registered User
    Join Date
    11-15-2012
    Location
    Middle East
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Vlookup function doesn't work on identical text

    I see now.

    Thank you very much Ron and everyone else.

+ 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. [SOLVED] Vlookup doesn't work - even with TRIM function
    By piper_co in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-08-2013, 08:07 AM
  2. Find function doesn't work with reference and non-visible text in cell (Excel2007)
    By Wiggert in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-25-2009, 09:12 AM
  3. Basic VLookup function doesn't work in large data set from msquery
    By eldoradotower in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-12-2008, 03:17 AM
  4. why doesn't vlookup function work?
    By lalaexcel in forum Excel General
    Replies: 9
    Last Post: 01-24-2006, 10:02 AM
  5. [SOLVED] Macro works, identical Function doesn't....why?
    By Paul S in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2005, 07:05 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