Results 1 to 6 of 6
Thread: Lookup Problem (2003)

20080126, 18:46 #1
 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 lookuparray, 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

20080126, 19:10 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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*(F10B28) returns 4.54747E13.
If you change the formula in B5 to =ROUND($B$4A5,1) and fill down, the lookup formulas will work.
You'll still have a problem with row 21, because G20 and G21 are equal.

20080126, 19:26 #3
 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

20080126, 19:32 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20080126, 20:54 #5
 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

20080127, 03:30 #6
 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