+ Reply to Thread
Results 1 to 7 of 7

Lookup formula, wrong values returned

  1. #1
    Registered User
    Join Date
    12-14-2017
    Location
    UK
    MS-Off Ver
    EXCEL 2013
    Posts
    6

    Lookup formula, wrong values returned

    Hello All,

    I am a beginner with Excel and am trying to learn a bit. Can anyone help me regarding the below problem?

    excel.PNG

    The "advance care" values surely should correspond to their values in column G?

    How have I gone wrong with this formula?

    Thanks in advance.

    Tom

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,621

    Re: Lookup formula, wrong values returned

    Hello Tom,

    LOOKUP requires the lookup range to be sorted, and may also give a "closest match". VLOOKUP is more appropriate here, try this:

    =VLOOKUP(B7,F$7:G$14,2,0)
    Audere est facere

  3. #3
    Valued Forum Contributor leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    1,997

    Re: Lookup formula, wrong values returned

    Hi Tom - The LOOKUP function requires a sorted lookup list to work correctly. Use VLOOKUP or INDEX/MATCH with exact match instead:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by leelnich; 12-14-2017 at 06:37 PM.
    Please click the Add Reputation star below any helpful posts, and mark your thread as SOLVED (Thread Tools, up top) once you have your answer. Thanks!-Lee

  4. #4
    Registered User
    Join Date
    12-14-2017
    Location
    UK
    MS-Off Ver
    EXCEL 2013
    Posts
    6

    Re: Lookup formula, wrong values returned

    Thanks DLL,

    Much appreciated

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,455

    Re: Lookup formula, wrong values returned

    Hi,

    If you're using LOOKUP you need to sort your lookup table into ascending order of column F.

    Personally I see no reason to use LOOKUP which was onlg kept for backwards compatability to much earlier software versions. Use VLOOKUP instead or, and what many of us prefer an INDEX(MATCH()) formula.

    Incidentally please upload actual workbooks rather than pictures which are rarely much use. For more difficult problems no one wants to recreate what you already have.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Registered User
    Join Date
    12-14-2017
    Location
    UK
    MS-Off Ver
    EXCEL 2013
    Posts
    6

    Re: Lookup formula, wrong values returned

    Thank you also Leelnich

  7. #7
    Registered User
    Join Date
    12-14-2017
    Location
    UK
    MS-Off Ver
    EXCEL 2013
    Posts
    6

    Re: Lookup formula, wrong values returned

    Hi,

    Thanks for the advice Richard.

    Will keep that in mind for any future queries

    Tom

+ Reply to 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