Explain strange VLOOKUP failure?

[mrblister]

	A	B	C
1	Time since start	Time difference	Result
2	0
3	0.01	0.01	10
4	0.02	0.01	10
5	0.03	0.01	#N/A
6	0.04	0.01	#N/A

<tbody>
</tbody>

OK, this one has me stumped.
Column A is just values.
Formula in B3 is A3-A2, copied down.
Formula in C3 is VLOOKUP(B3,LookupTable,35,FALSE), copied down. So C4 is VLOOKUP(B4,OutputGrid,35,FALSE), etc.

So how to explain the errors? It works fine for the first 2 iterations, then fails for... no reason? I tried using "Evaluate Formula" function, but in all cases everything looks identical, except in C5 (and beyond) the last step gives a #N/A instead of 10.

Some notes:

• LookupTable is on another sheet. It's pretty large
• LookupTable is a named range, but the failure persists even if I use regular range references.
• If I use VLOOKUP(B3,LookupTable,35,TRUE), this seems to work for some reason. Well, it should work, but FALSE should work as well, and it does for the first 2 instances.
• The value 0.01 definitely exists in LookupTable.
• The (leftmost) values in LookupTable are in ascending order.
• In this file, I previously had an error. Something along the lines of "/xl/calcChain.xml" was removed or something like that. I thought it was repaired, but later I noticed that if I added or removed a column from the sheet that LookupTable was on, Excel would be stuck forever in a calcuation loop. I fixed this, by copying all formulas on the sheet to another sheet. Maybe the issue is deeper than I suspected?

Ideas?

 

[StephenCrump]

Ideas?

Round your time differences to 2 decimal places.

 

[mrblister]

Thanks! I'll try it. But why does it make a difference?

 

[AhoyNC]

When you use FALSE in the VLOOKUP it looks for an exact match (0.01 would not equal say 0.011). When you use TRUE in the VLOOKUP it looks for a match or the closest match below the value (values must be in ascending order). As pointed out by StephenCrump you need to use the round function to get an exact match. Just changing a cells format to say two decimal places does not change the actual value in the cell, just what's displayed on the screen.

 

[StephenCrump]

Further to AhoyNC's comment ...

The difference may be very small, and not even apparent, because of the way Excel calculates and stores floating point numbers. See here, for example: https://support.microsoft.com/en-us/kb/78113

Your differences of 0.01 can't be represented exactly in binary.

Excel is smart enough to return TRUE for:
=0.03-0.02=0.01

So in my view is being a little inconsistent in returning #N/A rather than "Found it!" for:
=VLOOKUP(0.03-0.02,{0.01,"Found it!"},2,)

Hence the need to round to the precision of your VLOOKUP table:
=VLOOKUP(ROUND(0.03-0.02,2),{0.01,"Found it!"},2,) will return "Found it!"

 

[mrblister]

Geez... OK I get it now. Thanks for the explanations, this issue had me perplexed!