+ Reply to Thread
Results 1 to 7 of 7

vlookup producing wrong results

  1. #1
    Registered User
    Join Date
    03-30-2012
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    73

    vlookup producing wrong results

    hi all

    i have a vlookup sum that is looking at a list of UK postcodes and then shows the data in the 2 columns next to it, its providing the right result some times but sometimes it seems to be chosing the cell above my result.

    here are my 2 sums please let me know if there is anything wrong here or a better way of doing it


    =VLOOKUP(B28,sheet4!A2:E10579,2)

    =VLOOKUP(B28,sheet4!A2:E10579,3)

    kind regards

    rick

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,646

    Re: vlookup producing wrong results

    Try these:

    =VLOOKUP(B28,sheet4!A2:E10579,2,0)

    =VLOOKUP(B28,sheet4!A2:E10579,3,0)

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-30-2012
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    73

    Re: vlookup producing wrong results

    hi mate this is not working and producing a NA i have attached an example please have a go guys! test vlookup.zip

  4. #4
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: vlookup producing wrong results

    Please try this.

    A13 =LOOKUP(2^15,1/(SEARCH($E$7,Sheet2!$A$2:$A$10579)),Sheet2!A$2:A$10579)
    D13 =LOOKUP(2^15,1/(SEARCH($E$7,Sheet2!$A$2:$A$10579)),Sheet2!B$2:B$10579)
    G13 =LOOKUP(2^15,1/(SEARCH($E$7,Sheet2!$A$2:$A$10579)),Sheet2!C$2:C$10579)
    Please click 'Add reputation', if my answer helped you.

  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 producing wrong results

    Please check you PostCode database..
    In PostCode area.. approx all Postcode's contain.. 4-5 spaces..

    Remove them all.. Pete_UK's suggestion will work for you..

    To remove spaces.. in a separate cell use formula as Trim(A2) and drag toward down..
    Now Copy and paste - values in original location..
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  6. #6
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    Excel 2003-2019
    Posts
    837

    Re: vlookup producing wrong results

    hi,

    The main problem for producing a wrong result is due to the one less space.Hence in sheet1 eh9 give 1 more space so that its length will be 10 as currently it is 9.As in Sheet 2 the character counts of EH9 2 is 10 that leads to mismatch and produce error.This is perhaps due to your typo error.After giving one more space,use below formula-

    =VLOOKUP(E7,Sheet2!$A$2:$E$10579,3,FALSE)


    Hope this will solve your problem.

    With Best Rgds,

    suresh

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

    Re: vlookup producing wrong results

    =VLOOKUP(B28,sheet4!A2:E10579,2,false)

    =VLOOKUP(B28,sheet4!A2:E10579,3,false)
    now to fix column A of sheet 2 to remove trailing spaces
    select col A
    go to data tab or if you have 2003 or earlier just click data on the tool bar /text to columns/fixed width just click finish that should get rid of all those trailing spaces
    Last edited by martindwilson; 09-05-2013 at 08:25 AM.
    "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

+ 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] Index and Match combination producing the wrong result
    By Authentik8 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-10-2012, 11:01 AM
  2. Formula producing inconsistent results
    By joseclar in forum Excel General
    Replies: 5
    Last Post: 05-20-2009, 01:41 PM
  3. matching columns and producing results
    By ChrisVersion2 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-24-2007, 01:52 PM
  4. [SOLVED] formulas producing unjustified results
    By Query in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-11-2006, 06:50 AM
  5. [SOLVED] Help With Producing Results Please
    By Paul in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-18-2005, 01:06 PM

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