+ 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 Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    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.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - 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 - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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

    RIP - d. 06/10/2022

    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)

Similar Threads

  1. [SOLVED] VLOOKUP, 2 lookup values; then sum the lookup returned
    By pricepeeler in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-17-2014, 03:17 PM
  2. Multiple Values returned in a lookup
    By Droopy23 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-23-2013, 03:19 PM
  3. Using approximate lookup and getting duplicate returned values
    By nikkigotro in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2013, 02:54 PM
  4. [SOLVED] if statement help please. Wrong value being returned.
    By Mechjo16 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-26-2013, 10:29 PM
  5. Duplicate values returned using Index Match Lookup with Small()
    By jacob@thepenpoint in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-18-2013, 10:48 AM
  6. Lookup row for value and sum returned values.
    By TypeR in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-11-2007, 07:16 AM
  7. wrong date returned on ws.Cells(r,c).value
    By sbvb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2005, 10:59 AM

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