1. ## 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

2. ## 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. ## 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. ## 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. ## 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. ## 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
•