Skip navigation

LOOKUP returning unexpected results

815 Views 29 Replies Latest reply: Dec 16, 2012 1:19 PM by Barry Fass-Holmes RSS
  • Wayne Contello Level 6 Level 6 (12,580 points)
    Currently Being Moderated
    Dec 13, 2012 12:40 PM (in response to Barry Fass-Holmes)

    You should be able to make a sanitized version without any sensistive information that still dsmonstrates the problem.  That is all we need.  I am wanting to see some of the intermediate results used in the formula.  So playing with this directly would be easier.

  • Jeff Shenk Level 4 Level 4 (2,000 points)
    Currently Being Moderated
    Dec 13, 2012 12:40 PM (in response to Barry Fass-Holmes)

    Naturally it returns "NO Match;" you asked for the result in the 22nd column of a single column search range.

     

    =IFERROR(VLOOKUP($B2,'small table' :: $B$2:$W$230,22,FALSE),"NO Match")

     

    should get the result.

  • Jeff Shenk Level 4 Level 4 (2,000 points)
    Currently Being Moderated
    Dec 13, 2012 12:45 PM (in response to Barry Fass-Holmes)

    I would guess that the one result that got an error was looking for an ID that was lower than any of the ones in the search range (no "close match").

  • Jeff Shenk Level 4 Level 4 (2,000 points)
    Currently Being Moderated
    Dec 13, 2012 12:52 PM (in response to Jerrold Green1)

    Jerry,

     

    Doesn't MATCH do a close match unless you give it a third parameter?

  • Wayne Contello Level 6 Level 6 (12,580 points)
    Currently Being Moderated
    Dec 13, 2012 1:02 PM (in response to Jeff Shenk)

    Screen Shot 2012-12-13 at 3.01.23 PM.png

  • Jeff Shenk Level 4 Level 4 (2,000 points)
    Currently Being Moderated
    Dec 13, 2012 1:16 PM (in response to Wayne Contello)

    Wayne,

     

    From the iWork '09 Missing Manual:

     

    "There's a catch, though, …LOOKUP doesn't know how to do an exact match. It always uses the close-match method…"

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    Dec 13, 2012 1:41 PM (in response to Jeff Shenk)

    Jeff,

     

    There are matching method parameters in Match:

     

    Find largest value (1 or omitted):  Find the cell with the largest value less than or equal to search-for. Wildcards can’t be used in search-for.

     

    Find value (0):  Find the first cell with a value that exactly matches search-for. Wildcards can be used in search-for.

     

    Find smallest value (–1):  Find the cell with the smallest value greater than or equal to search-for. Wildcards can’t be used in search-for.

     

    Since I never use this method for anything other than strings, I haven't had a problem, or didn't notice it if I did. That's why I'd like to see the sample file. I wouldn't be a problem to add the "0" parameter to force the exact match.

     

    Jerry

  • Jeff Shenk Level 4 Level 4 (2,000 points)
    Currently Being Moderated
    Dec 13, 2012 1:50 PM (in response to Barry Fass-Holmes)

    Barry,

     

    Check my comment above: for VLOOKUP, the range has to include both the search terms and the answers.

  • jaxjason Level 4 Level 4 (3,320 points)
    Currently Being Moderated
    Dec 14, 2012 12:10 PM (in response to Barry Fass-Holmes)

    I appologize if it came across wrong, I wasnt implying you hadnt searched, but was pointing you to the formula pdf that has every formula available and can be kept at the ready in iBooks.

     

    The way vlookup works is you "look up" a value in the left hand column of a range and bring back some value in one of the columns on that row the match is found. The range you use is not being compared, but is the list of columns you might want to bring back from when a match is found in the left hand column.

     

    So if you know the name Jason is found in Column B (first names), and you want the value in the C column brought back, you would feed it the range "B1:C100" (or whatever your last row is, you can also reference the whole column "B:C") and ask for the second column (B being the first column).

     

    =vlookup(X1,$B:$C,2,false)

     

    So in your example, we are looking for whatever is located in Column B for this row, look in the "small table" columns B:W (which holds all my data, column B being where my item I am using for look up is), and when you find a match, go over to the 22nd column in that range i gave (B2:W230) on that row adn bring its value back.

     

    Does that help clear it up? I know vlookups confuse alot of people i get at least a half dozen questions a month on it where i work.

     

    Let me know if you need more examples, I can throw a spreadsheet together and email it to you.

     

    Jason

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.