Grant Crosthwaite
Level 1 (29 points)
Wireless

Q: Vlookup gives unexpected/erroneous results

HI Guys

 

I used to think I had this spreadsheet thing down pat but I find myself up after midnight wrestling with a VLookup problem! I'm really hoping you can point me to the error of my ways..

 

I Am a convert from Excel so I may be caught in a mind control thing. I have built a fairly large spreadsheet to do lots of hobby related stuff. I give it away to people who do the same sort of thing as I do. It calculates stuff for soap making but I have just discovered that it gets some stuff wrong - which is potentially dangerous.

 

image.png

 

I have a simplified version of what of what is happening in the image above. In the cell F2 I have: VLOOKUP(E2,Lookup Values:Results,2,close-match).

 

Now, I would expect Numbers to look down column B to find the value in cell E2 (1.90) then deliver the corresponding value from column C (19.0). However, it doesn't do this. Instead it delivers the value from the cell above as if it couldn't find a match.

 

I have tried every setting I can think of to no avail. I have set every cell on the table above to a number with 2 decimal places. I even changed exact match and close match but that just made things worse.

 

I know it is going to be something that will cause me to kick myself but I need to fix it so no-one gets hurt. Any and all help gratefully received.

 

Cheers, Grant

 

PS I think I should mention that I'm doing this on an iPad Air 2 which is a bit new to me. And I just noticed that I am posting in Numbers for Mac. I'm sorry for that but I believe the versions are supposed to have the same capabilities?

iPad Air, Other OS, Latest iOS & Numbers app.

Posted on May 28, 2016 8:42 AM

by t quinn,Solvedanswer
t quinn t quinn
Level 5 (5,002 points)
Mac OS X
A:

Hi Grant,

 

I am sorry to say that I can duplicate your results.

Screen Shot 2016-05-28 at 11.22.58 AM.png

I used the Numbers approach and separated the lookup table. I created it by hand entering the first two numbers in a column and dragging down. With my in/out table I did the same in A and then dragged the formula down in B. I changed it to look for an exact match. Things looked fine until I changed A3 to 1.8 then I get an error. I revised the formula in A2= INDEX(lookup::B,MATCH(A2,lookup::A,0)) which will do the same thing as VLOOKUP(). And it does- it throws an error.

Screen Shot 2016-05-28 at 11.32.25 AM.png

 

If I retype "2.1" or "1.8" into the lookup table the error goes away and things behave as they ought.

 

I think this is a genuine bug built in to the way that Numbers fills number sequences. I suggest a bug report to Apple.

 

quinn

Posted on May 28, 2016 7:30 PM

Close
Grant Crosthwaite

Q: Vlookup gives unexpected/erroneous results

  • All replies
  • Helpful answers

  • by Jeff Shenk,

    Jeff Shenk Jeff Shenk May 28, 2016 9:43 AM in response to Grant Crosthwaite
    Level 4 (2,803 points)
    May 28, 2016 9:43 AM in response to Grant Crosthwaite

    I suspect that you are using calculated values, rather than hand entered ones in your "Lookup Values" column, and even though you have formatted the column to show two decimal places, because of the approximations that occur when doing decimal calculations on a computer that uses binary math, the actual values which the VLOOKUP uses differ slightly many decimal places further down the line. If you wrap each calculation in a ROUND function to change the actual value to two decimal places, so the cell actually contains exactly what your formatting displays, you will probably get the expected result.

  • by t quinn,Solvedanswer

    t quinn t quinn May 28, 2016 7:30 PM in response to Grant Crosthwaite
    Level 5 (5,002 points)
    Mac OS X
    May 28, 2016 7:30 PM in response to Grant Crosthwaite

    Hi Grant,

     

    I am sorry to say that I can duplicate your results.

    Screen Shot 2016-05-28 at 11.22.58 AM.png

    I used the Numbers approach and separated the lookup table. I created it by hand entering the first two numbers in a column and dragging down. With my in/out table I did the same in A and then dragged the formula down in B. I changed it to look for an exact match. Things looked fine until I changed A3 to 1.8 then I get an error. I revised the formula in A2= INDEX(lookup::B,MATCH(A2,lookup::A,0)) which will do the same thing as VLOOKUP(). And it does- it throws an error.

    Screen Shot 2016-05-28 at 11.32.25 AM.png

     

    If I retype "2.1" or "1.8" into the lookup table the error goes away and things behave as they ought.

     

    I think this is a genuine bug built in to the way that Numbers fills number sequences. I suggest a bug report to Apple.

     

    quinn

  • by SGIII,

    SGIII SGIII May 28, 2016 11:12 AM in response to Grant Crosthwaite
    Level 6 (10,637 points)
    Mac OS X
    May 28, 2016 11:12 AM in response to Grant Crosthwaite

    When using 'exact match' with VLOOKUP and its close cousin the INDEX MATCH combination (which offers more matching options) typically you'll want to look up text in a column containing text.

     

    Looking up an exact match on a number might give you the results you want sometimes, but not always.  Using numbers with the 'close match' options is useful in cases where want to look up a value if the number is within a range, with the ranges laid out in the lookup values column.

     

    Here, where you want an exact match, just enter your Input as text and format your Lookup values as text and all will be well.  (I entered two sequential numbers, selected the cells, and filled down, then converted the Data Format to Text, and had no problems. Note how after formatting the Lookup values as text they naturally align left. For the Input values I just typed a ' before the number so Numbers recognized them as text, automatically left aligning them too.)

     

    Screen Shot 2016-05-28 at 1.53.09 PM.png

     

    =VLOOKUP(E3,Lookup values:Results,2,FALSE)

     

    or, more flexible:

     

    =INDEX(Results,MATCH(E4,Lookup values,-1))

     

    SG

  • by Grant Crosthwaite,

    Grant Crosthwaite Grant Crosthwaite May 28, 2016 6:59 PM in response to Jeff Shenk
    Level 1 (29 points)
    Wireless
    May 28, 2016 6:59 PM in response to Jeff Shenk

    Thanks Jeff,

     

    Not calculated values. I typed the values in the 1st 2 cells then filled down. I think t quinn is right and it is a bug in the fill down sequence.

     

    ON an unrelated note, this forum is horrible to type in on an iPad screen. Double capitals every sentence. Very difficult to highlight and edit. Or is it just me again?

     

    THanks. 

  • by SGIII,Helpful

    SGIII SGIII May 28, 2016 7:25 PM in response to Grant Crosthwaite
    Level 6 (10,637 points)
    Mac OS X
    May 28, 2016 7:25 PM in response to Grant Crosthwaite

    Grant Crosthwaite wrote:

     

    Thanks Jeff,

     

    Not calculated values. I typed the values in the 1st 2 cells then filled down. I think t quinn is right and it is a bug in the fill down sequence.

     

     

     

    Hi Grant,

     

    Not sure if you saw my post. As far as I can tell here, the fill down sequence is ok. Try formatting the filled down cells as text and enter your lookup value (Input) as text and see if you get the exact match that you want.

     

    If your work relies on lookups a lot, it would pay to study the differences between the various matching methods, of which VLOOKUP offers two and MATCH three.  Here's from Help > Formulas & Functions Help  for MATCH.  You can also look it up in the function browser on your iPad.

     

    Screen Shot 2016-05-28 at 10.13.40 PM.png

     

    With 'find value' you'll have the most reliable results using text.  'find largest value' and 'find smallest value' are useful for situations where you have numerical values in a column that you want to represent a series of ranges (for something like looking up A for numbers 90-100, B for 80-89, C for 70-89, etc.)

     

    I've posted here a few times on the iPad. Much to use the Mac!

     

    SG

  • by Grant Crosthwaite,

    Grant Crosthwaite Grant Crosthwaite May 28, 2016 7:46 PM in response to SGIII
    Level 1 (29 points)
    Wireless
    May 28, 2016 7:46 PM in response to SGIII

    HI SG,

     

    thanks for the input. I did see your answer and went off to check a few things. I appreciate that changing the format of my list of numbers to text might solve my immediate problem. It isn't very intuitive though and so not very satisfying.

     

    THere here is a problem in the fill down functionality as I have attempted to demonstrate in this image:

    image.png

     

    COlumn A had all values typed manually. Column B had them filled in after the first two. It all works fine until the tenth value "1.9" in column B.

     

    THe the only difference in the data is how it was entered. I'd call that a bug. Now, if I'm to report this to Apple can anyone tell me where I send it and the best way to do it?

     

    i Will take your advice and learn more about the different methods for lookups. I even bought a book on Numbers which I'll be sure to read Now.

     

    thanks for your help and I'm sorry for the crazy capitalisation. This forum is weird on my iPad.

     

    CHeers,

     

    grant

  • by SGIII,

    SGIII SGIII May 28, 2016 7:56 PM in response to Grant Crosthwaite
    Level 6 (10,637 points)
    Mac OS X
    May 28, 2016 7:56 PM in response to Grant Crosthwaite

    If it's a bug, I don't think it's in the fill-down.  It might be in the lookup functions, where you seem to be using them in a way that may not be all that common.

     

    I'm curious, why are you using a number to look up exact matches? If we know more about why you're doing that, there may be an easier way.

     

    The most common use case for exact matches is to look up text and return a number (or text) from another column.  The most common use case for the other types of matches is, as posted, when you're using a column of numbers as a series of ranges.

     

    SG

  • by Grant Crosthwaite,

    Grant Crosthwaite Grant Crosthwaite May 28, 2016 8:17 PM in response to SGIII
    Level 1 (29 points)
    Wireless
    May 28, 2016 8:17 PM in response to SGIII

    HI SG

     

    I'll have to wait until I get back to my computer to see if I can find an example of when I'm using it. I'd have thought that it was the fill down as the lookup is working in one set of data but not the other. The only difference is the entry method.  

  • by Jeff Shenk,

    Jeff Shenk Jeff Shenk May 29, 2016 5:36 AM in response to Grant Crosthwaite
    Level 4 (2,803 points)
    May 29, 2016 5:36 AM in response to Grant Crosthwaite

    Grant,

     

    I would assume that Fill Down calculates the difference between the first two cells and then adds that value to the cell above to calculate each subsequent value. Not an explicit calculation on your part, but the application has to calculate the values.

    Screen Shot 2016-05-29 at 8.20.09 AM.png

    In the example above I used Fill Down (by dragging the handle) to populate Column B. The value in C11 was typed exactly as it appears and the formula in cell D11 can be seen in the Formula Bar. The "filled down" value is running slightly "high" about 16 decimal places down.

     

    In cell E11, the formula is =(ROUND(B11, 2)) - C11, which gets rid of the tiny discrepancy.

  • by Grant Crosthwaite,

    Grant Crosthwaite Grant Crosthwaite May 29, 2016 5:44 AM in response to SGIII
    Level 1 (29 points)
    Wireless
    May 29, 2016 5:44 AM in response to SGIII

    image.png

     

    OK. So here is a screenshot of an example table. All it is trying to do is allow someone to make a selection on another sheet, of a value from a list in this case, which will return a calculated value from this sheet. So, someone selects a number on the Recipe sheet that relates to the ratio of Sodium Hydroxide to water that they want to use in a recipe. This value will be found in column A. Depending on which other ingredients they choose elsewhere the values in columns B and C will change. Thus the lookup.

     

    As you have suggested, there may well be a better way to do this. I'd be delighted to learn something different - especially if it is better. Naturally, I would also want to understand why it is better so I know how to choose the method.

     

    i hope this gives you an example of the type of thing I am trying to do. I should point out that I almost never do an exact match in these things. In fact I'm pretty sure that I never have. Any time I've tried it (to see what it does) it results in an error. So, I just leave it at the default setting "close match".

  • by t quinn,

    t quinn t quinn May 29, 2016 7:00 AM in response to Grant Crosthwaite
    Level 5 (5,002 points)
    Mac OS X
    May 29, 2016 7:00 AM in response to Grant Crosthwaite

    Hi Grant,

     

    I think Jeff has a good explaination as to the mechanics here. Your LOOKUP() close-match takes the value that is less than or equal to your 1.9. Unfortunately, the poplated column has a number slightly more than the displayed 1.9 so it takes the number above.

     

    SG's solution of comparing text values is a good one. Another way to do this is to create a popup from your values in the lookup table and use the popup in your other table (Recipe, I think). My formula remains "exact match".

    Screen Shot 2016-05-29 at 7.42.17 AM.png

    This will simplify things for your users also.

     

    You can use INDEX/MATCH unless your users may input a value greater than your lookup

    Using INDEX/MATCH

    "find value" returns an error

    "find largest value" returns the same as VLOOKUP() "close match"

    "find smallest value" returns what we want. "smallest value" is greater than or equal to the search-for.

    Screen Shot 2016-05-29 at 7.32.48 AM.png

     

    quinn

  • by Grant Crosthwaite,

    Grant Crosthwaite Grant Crosthwaite May 30, 2016 12:47 AM in response to SGIII
    Level 1 (29 points)
    Wireless
    May 30, 2016 12:47 AM in response to SGIII

    HI SG and quinn,

     

    Your guys have been really helpful. I am incredibly grateful for both your time. I now have several workable solutions to the issue I was having. I'm curious as to whether you would consider Numbers apparent inability to do simple arithmetic a "bug"? If the fill down function is giving an error like this it reminds me of the floating point issue excel had with the change to Pentium processors.

     

    Lsalty, is there an online resource that you can point me to where I can learn about ALL of the the functions in Numbers? As I said before, I have a book which only covers a few of these. I'd reallly like to learn the differences between the various ways of doing something like a lookup - so index + match vs vLookup for instance. I've had a look myself but nothing that explains things as clearly as you have here on this forum. Perhaps that is a book you should be writing?

     

    Abyway, many thanks again for all your help on this. You are awsome.

     

    Cheers,

     

    Grant

  • by Grant Crosthwaite,

    Grant Crosthwaite Grant Crosthwaite May 30, 2016 1:52 AM in response to Grant Crosthwaite
    Level 1 (29 points)
    Wireless
    May 30, 2016 1:52 AM in response to Grant Crosthwaite

    Please ignore my last question. I found this manual which seems helpful (if not as clear as you guys are - so maybe you should be writing that book).

     

    Thanks again. Cheers, Grant

  • by SGIII,

    SGIII SGIII May 30, 2016 9:45 AM in response to Grant Crosthwaite
    Level 6 (10,637 points)
    Mac OS X
    May 30, 2016 9:45 AM in response to Grant Crosthwaite

    That "manual" is for an 7-year-old version of Numbers. It covers most, but not all, of the functions now available in Numbers. For the latest just go to Help > Formulas and Functions Help in your menu. You can also get explanations and usage examples via the formula editor. Most functions work the same in Excel so you can also do a web search for a particular function.

     

    And be sure to check out the templates at File > New in your menu. Good examples there illustrating how to accomplish things in Numbers. Gradebook and GPA in particular use lookups.

     

    SG