Closed Thread
Results 1 to 22 of 22

VLOOKUP issue

  1. #1
    Registered User
    Join Date
    06-13-2006
    Posts
    10

    VLOOKUP issue

    I have a problem in getting VLOOKUP to evaluate consistently. It is best understood from the attached file (Office 2010), where cells B3 and B4 are not showing the expected values.

    The screenshot is also at Book2 | Flickr - Photo Sharing!

    This is just a small segment of the big excel sheet, and so a global solution is required.

    How to get it right please?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: VLOOKUP issue

    Hi, abanerji

    Remove (') on Column A.
    Click (*) if you received helpful response.

    Regards,
    David

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: VLOOKUP issue

    Data in A column are in text format and in F column are in Number format. If both are in same format you will get result.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: VLOOKUP issue

    Hi abanerji,

    It is because A3 and A4 are text, not numbers :-
    use below formula:-
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  5. #5
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: VLOOKUP issue

    Use this for check the value on column A are text or number:
    This code combine your code with code from dilipandey
    Please Login or Register  to view this content.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: VLOOKUP issue

    Hi abanerji,

    Here is a change in my above approach.
    Type 1 in any free cell, copy that and select data in column A, do paste special - values- multiply and now your own formula should work


    Regards,
    DILIPandey
    <click on below * if this helps>

  7. #7
    Registered User
    Join Date
    06-13-2006
    Posts
    10

    Re: VLOOKUP issue

    Unfortunately, removing label indicator manually is not practical. Do you have a global solution please?

    Quote Originally Posted by SDCh View Post
    Hi, abanerji

    Remove (') on Column A.

  8. #8
    Registered User
    Join Date
    06-13-2006
    Posts
    10

    Re: VLOOKUP issue

    Unfortunately, this is a data dump from Navision ERP, which does not seem changeable at source as per technical people.

    Quote Originally Posted by kvsrinivasamurthy View Post
    Data in A column are in text format and in F column are in Number format. If both are in same format you will get result.

  9. #9
    Registered User
    Join Date
    06-13-2006
    Posts
    10

    Re: VLOOKUP issue

    Thanks for all your posts. I got a simple solution at http://www.mrexcel.com/forum/excel-q...kup-issue.html

    Quote Originally Posted by dilipandey View Post
    Hi abanerji,

    Here is a change in my above approach.
    Type 1 in any free cell, copy that and select data in column A, do paste special - values- multiply and now your own formula should work


    Regards,
    DILIPandey
    <click on below * if this helps>

  10. #10
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VLOOKUP issue

    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  11. #11
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: VLOOKUP issue

    Nice research Fotis


    Regards,
    DILIPandey
    <click on below * if this helps>

  12. #12
    Registered User
    Join Date
    06-13-2006
    Posts
    10

    Re: VLOOKUP issue

    Sorry, if cross-posting offended you. But, when my company wants a quick answer (like yesterday), I am forced to seek the most suitable answer.

    Now, please do not be sarcastic ... I can also be the same.

    Going back to the "most suitable answer", what appeared in mrexcel forum was the simplest. If I had restricted myself to forum 'A' only, I would have been toiling with some huge formulae, with chances of inadvertent error, debugging ... we all know it, no?

    Back to the issue of cross-posting ... in my google search for a ready answer, I did not come across one, and so posted in multiple fora. In the past, when there has been some security issue, I have posted in different fora, and the knowledge derived has been phenomenal to solve the issue. I appreciate and respect that others may have a different point of view.

    In conclusion, I do thank people whose time has helped me in solving my problems.

    Quote Originally Posted by Fotis1991 View Post

  13. #13
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VLOOKUP issue

    I think you didn't read the article in the last link that i provided.

    http://www.excelguru.ca/content.php?...-cross-posters

    Also rule 8 of this forum, says:


    8. Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.
    Number 3 rule in Ozgrid, says:
    ...Do not cross-post without supplying a link to the duplicate question on the other forum.
    And.. YES, for all these reasons that you'll find in that article: I don't like the cross posters.

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VLOOKUP issue

    Abanerji,

    Fotis is quoting the forum rules here and is not trying to be sarcastic. There is a reason why cross posting is not allowed in most forums. If you read the link that he provided, you will understand.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  15. #15
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: VLOOKUP issue

    I just took the easy way, an unrepentant cross poster gets added to my ignore list..
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  16. #16
    Registered User
    Join Date
    06-13-2006
    Posts
    10

    Re: VLOOKUP issue

    Thank you for your message. Yes, I did read the link that Fotis provided. Unfortunately, I was in a hurry to get a solution, because of the workplace pressure (more so, when in-house resource could not help). I assure you that in future, if I cross-post in these forums I shall transparently provide the links to the extent possible. To clarify, if I post in Forum 'A' first, Forum 'B' next, and Forum 'C' last, my post in Forum 'A' will not have the links to Forums 'B' and 'C' till I post in the last two. So, I shall get back to Forum 'A' and edit my OP, to provide 'B' and 'C' links, and so on. Hope this is ok, and within the rules.

    As to my comment on Fotis' sarcasm, it was not against his pointing out the rules, but because what he said in this line, viz., "Very nice....... So many people in 3(only???) forums work for you....". I am afraid that this falls in offensive category.

    Quote Originally Posted by arlu1201 View Post
    Abanerji,

    Fotis is quoting the forum rules here and is not trying to be sarcastic. There is a reason why cross posting is not allowed in most forums. If you read the link that he provided, you will understand.

  17. #17
    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
    44,091

    Re: VLOOKUP issue

    "Very nice....... So many people in 3(only???) forums work for you....". I am afraid that this falls in offensive category.
    And possibly the tired and frustrated category. How do you think Fotis discovered that you were cross posting? Because he's answering threads in all those other forums too.

    Now, please do not be sarcastic ... I can also be the same.
    Sure you can. But you're the one needing an answer. So, I think your sarcasm might just not be very helpful to your cause.

    Thanks for all your posts. I got a simple solution at http://www.mrexcel.com/forum/excel-q...kup-issue.html
    Good that you got a reply from that other forum. Be sure to go there first next time.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    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


  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VLOOKUP issue

    Nice idea Alf - regarding ur post which is now post 20.
    Last edited by arlu1201; 03-11-2013 at 09:53 AM.

  19. #19
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VLOOKUP issue

    ...........................

  20. #20
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: VLOOKUP issue

    @ dreadwolf

    Nice to know the somebody admits to an "Ignore list" myself I call it the "Waste of Time and Effort list" but I'm considering calling it the "Raven List" i.e. after Edgar Allan Poe's poem "The Raven" where the refrain goes "Quoth the raven, Nevermore."

    Reading threads I myself adds OP's that don't follow forum rules to my "Waste of Effort" list and also OP's disappearing without thanking the forum member and not marking the thread "Solved" as well. New posters get a special treatment but if they don’t follow Forum rules after the 10th posing then !!!

    Perhaps it's time to make an official "Ignore" list advising OP's that certain behaviour is not acceptable and breaking forum rules will place you on the "Ignore" list with the effect that you don't get any help from forum members?

    Alf

  21. #21
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: VLOOKUP issue

    Yeah... wrong post

    Note:
    Even we have a long list of "Ignore" list, OP's can make other username and will end up with the same situation....
    Last edited by SDCh; 03-11-2013 at 09:58 AM. Reason: Add note

  22. #22
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VLOOKUP issue

    In light that this question has been answered and most of the continued ensuing discussion is actually off-topic (although deserved, dude, don't crosspost without links, don't defend it when it offends those who help for free.).... I am closing this thread.


    You have 10 posts now, we expect you to know and abide the Forum Rules that we all follow. Read them (menu bar above).
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

Closed Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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