+ Reply to Thread
Results 1 to 18 of 18

VLOOKUP Error

  1. #1
    Registered User
    Join Date
    04-04-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    VLOOKUP Error

    Hi,

    I am new to this forum. I am stuck with an issue and I would like to get some help on it, please.


    I have an a VLOOKUP formula, which doesn't seem to work for just 1 value.
    The strange fact is that when I type the lookup value rather than looking at a cell, it works.
    Any idea?
    All the columns are formatted as general. I tried to do Len() and it returns the correct number. This is just to make sure that there are no invisible characters in the cells

    Unfortunately, I am not able to attach my file in this forum.

    Any help is much appreciated

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    01-08-2011
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2007 to 2016
    Posts
    456

    Re: VLOOKUP Error

    Hi and welcome to the forum

    Could you just attach a small sample from your workbook so that we can have a look? Please anonymise the data first, but leave the "offending" item in place if possible.

    This kind of thing is usually caused by there being a mis-match between the types of data (numbers which are actually text is a common one), but without seeing the actual data it's kind of hard to be sure.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: VLOOKUP Error

    Assume the lookup cell is in A1
    Locate the value in the lookup table manually, e.g. B371

    in a blank cell enter
    =(A1=B371)

    If it says FALSE then the values arent the same
    =LEN(A1)
    =LEN(B371)
    ISTEXT(A1) ISNUMBER(A1)
    ISTEXT(B371) ISNUMBER(371)
    as youve partly done should be able to determine why they arent the same
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    04-04-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    Re: VLOOKUP Error

    Hi,

    I wanted to attach a file. But I do not see the option at all here?
    Is it that is disabled for me?

  5. #5
    Registered User
    Join Date
    04-04-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    Re: VLOOKUP Error

    Hi,

    I wanted to attach a file. But when I click the attachment button, nothing happens. I mean I do not get an option to choose which file to attach or anything.
    Is it disabled for me?

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: VLOOKUP Error

    did you try what Special-K recommended?
    to attach, "go advanced" below this box...
    then "manage attachments"
    then "choose file"
    then "upload" - hint, best place to find quickest is to save it to desktop
    then "close this window"
    then if you want check preview post to see it is attached or just submit reply.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  7. #7
    Registered User
    Join Date
    04-04-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    Re: VLOOKUP Error

    Hey,
    Thanks!
    Now I realised what is wrong.
    One is text and the other isn't it
    How do I resolve it?

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: VLOOKUP Error

    it depends on why it is text. Can you change the cell to number or general format? if the length is the same and there are no spaces, sometimes just clicking inside the cell then hit return and it will change. sometimes just replacing one number in it will do it.

  9. #9
    Registered User
    Join Date
    04-04-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    Re: VLOOKUP Error

    Here is the attachment
    Attached Files Attached Files

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: VLOOKUP Error

    on LOOKUP sheet select A1:A5, from DATA menu select Text to Columns and hit Finish
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: VLOOKUP Error

    yes, I just clicked in both A3 and A4 of your summary sheet and then clicked out and both changed to numeric and then the vlookup worked.
    Sometimes I just do the easy way when I have a big file of a lot of cells that are text and they won't match a column of numbers. I do find 0, replace all 0 and that converts those with a 0 from text to number. I repeat it with each number through 9.
    But in your case you can change your vlookup formula to this and it should work. =VLOOKUP(--A3,Lookup!A:B,2,FALSE)

  12. #12
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: VLOOKUP Error

    multiply the text * 1 to make it a value.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  13. #13
    Registered User
    Join Date
    04-04-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    Re: VLOOKUP Error

    I get the lookup values from a different source. There are many values (probably around 100). I paste the data into my file, and the lookup should pick up automatically. This is what I want to do.
    So I am not sure why one particular value is not text while the others are not

  14. #14
    Registered User
    Join Date
    04-04-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    Re: VLOOKUP Error

    Thanks a lot, all of you.
    This multiplication solved my problem.
    Now use the VLOOKUP as follows:
    VLOOKUP((A1*1),Lookup!A:B,2,FALSE)

    and it works!!!!




    Quote Originally Posted by oeldere View Post
    multiply the text * 1 to make it a value.

    See the attached file.

  15. #15
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: VLOOKUP Error

    Use method from #10 (it will make all cells in the same format, text), then use one of method from #11 or #12

  16. #16
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: VLOOKUP Error

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

    It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution. (instead of sending a private message)

  17. #17
    Registered User
    Join Date
    04-04-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    Thumbs up Re: VLOOKUP Error

    ok. Thanks

  18. #18
    Registered User
    Join Date
    04-04-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    Thumbs up Re: VLOOKUP Error

    Thank you!

    Quote Originally Posted by sandy666 View Post
    Use method from #10 (it will make all cells in the same format, text), then use one of method from #11 or #12

+ 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. Concatenate error across 2 worksheets with vlookup error
    By COGICPENNY in forum Excel General
    Replies: 2
    Last Post: 11-30-2015, 07:56 PM
  2. Iferror vlookup if error vlookup if x=Y,
    By rwmeis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2013, 08:24 PM
  3. How to error trap a vlookup that returns an error or #N/A
    By kjy1989 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2013, 12:01 PM
  4. [SOLVED] lookup - if(is error(vlookup and error messages meanings
    By grphillips in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-18-2013, 07:03 PM
  5. Replies: 0
    Last Post: 05-14-2012, 11:59 PM
  6. error on vlookup
    By isabella in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 11-11-2010, 09:53 PM
  7. Vlookup error
    By hermithead in forum Excel General
    Replies: 1
    Last Post: 04-14-2010, 06:22 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