+ Reply to Thread
Results 1 to 5 of 5

Look up function returning rogue values :-(

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    York
    MS-Off Ver
    Excel 2010
    Posts
    22

    Look up function returning rogue values :-(

    Good afternoon from 12 degrees C wetherby UK :-)

    I have two columns. One column has text values the second numeric. All i want to do is get excel to return values if they match a specific text field. For example if a column says "ask" (Text Data) i want to return its associated numeric value in column B.

    Here is the formula Ive been using:
    =LOOKUP("ask",A1:B28)

    Here is an illustration of the actual problem:

    http://i216.photobucket.com/albums/c...psfc79fbcc.jpg
    But i just cant get me head around why it does not return the correct value.

    Any help much appreciated, I feel ground down by what i thought would be straight forward :-(

    Grazie tanto,
    David

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Look up function returning rogue values :-(

    hi David, welcome to the forum. LOOKUP requires the data to be sorted. try:
    =VLOOKUP("ask",A1:B28,2,0)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Look up function returning rogue values :-(

    Hi,
    There is a third argument called Range_Lookup that you need to set to false.

    See http://office.microsoft.com/en-us/ex...005209335.aspx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    01-22-2013
    Location
    York
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Look up function returning rogue values :-(

    Thanks Benish that worked but may I ask about data sorting. The 2,0 on the end of =VLOOKUP("ask",A1:B28,2,0) is new territory for me. How does this work please I cant get my head around how this bit fixed it.

    Thanks in advance :-)

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Look up function returning rogue values :-(

    LOOKUP always needs the "lookup range" (A1:A28 in your case) to be sorted ascending (if it isn't sorted you may get "unexpected results" which is what you experienced). Even when sorted you might get a "closest match", i.e. if "ask" doesn't exist in A1:A28 it would match with the next value alphabetically. It doesn't seem like you would want that here, so VLOOKUP with FALSE as 4th argument is the answer:

    With this formula

    =VLOOKUP("ask",A1:B28,2,FALSE)

    You don't need to sort anything and the FALSE at the end indicates that you will get an exact match only (you can use zero in place of FALSE too). In this case if "ask" doesn't appear you get #N/A error. The 2 tells excel to extract the result from the second column of the range indicated.
    Last edited by daddylonglegs; 04-30-2013 at 10:59 AM.
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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