Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup Problem (2003)

    I have a simple lookup problem where the lookup array has a column with formulas. I try both the index&match method and the normal vlookup method. The first two lines are OK the rest not.
    The simplified problem is in the attached workbook, tab N1 with formulas in the lookup-array, N2 with the formulas copied and pasted as values. But in the latter case there are also problems. There is an explanation of the problem in the workbook.
    I have no idea what goes wrong and any idea/advice is welcome.

    Regards, Teunis
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Problem (2003)

    The calculations introduce tiny rounding errors, making the numbers in column B not quite match those in column F.
    Even though the formula =F10=B28 returns TRUE, the formula =1*(F10-B28) returns 4.54747E-13.
    If you change the formula in B5 to =ROUND($B$4-A5,1) and fill down, the lookup formulas will work.
    You'll still have a problem with row 21, because G20 and G21 are equal.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Problem (2003)

    Hans,

    Thanks. I thought about this all the time, but when I even when set the number of digits in column B in N1 to 30, there are all zeroes after the first digits except for B84, B54 and B86. Note that the figures in column B are not dependent on the previous Cell, so I thought that this simple formula would do no harm. Apparently it I was wrong. As for G20 and G21, I do not understand, the flows are only for comparison. The lookup value is the RS not the flow.

    Regards, Teun

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Problem (2003)

    The lookup values in F20 and F21 are different, and hence the formulas in I20 and I21 return different results. But the flow in G20 and G21 is the same, so the test in J21 will return FALSE.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup Problem (2003)

    The number of displayed digits is different than rounding. Also the display is in base 10 while the comparisons are done with 15 digits of base 2 numbers. There is also rounding difference between display and computer

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Problem (2003)

    Hans,
    Thanks. I applied your advice and added =ROUND(expression,1) to all formulas in column B of N1 and now it's OK and does what I want.

    Steve,
    Thanks for letting me know the theoretical background. I should have known, but I am too long out of programming.

    Regards, Teunis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •