Vlookup not working for matching with numbers - Is it a bug?

[NTLH_Excel]

Dear Experts,

I currently got a strange problem with Vlookup for number matching. Here is the data sample in my file:

0.0000000​	0​				0.0000000​
0.1000000​	0.1​				0.1000000​
0.2000000​	0.2​				0.2000000​
0.3000000​	#N/A​				0.3000000​
0.4000000​	0.4​				0.4000000​
0.5000000​	0.5​				0.5000000​
0.6000000​	0.6​				0.6000000​
0.7000000​	0.7​				0.7000000​
0.8000000​	#N/A​				0.8000000​
0.9000000​	#N/A​				0.9000000​
1.0000000​	#N/A​				1.0000000​
1.1000000​	#N/A​				1.1000000​
1.2000000​	1.2​				1.2000000​
1.3000000​	1.3​				1.3000000​
					1.4000000​
					1.5000000​
					1.6000000​
					1.7000000​
					1.8000000​
					1.9000000​

The problem is:

+ I have numeric data at column A, started from 0 at cell A2, and I use auto fill (0.1 increase each row) function from Excel to get column A filled in with 0, 0.1, 0.2, 0.3, ....
+ At column F, I have manually entered with 0 at F2, from F3, I use formula to increase by 0.1 (i.e F3=F2+0.1, and so on)

Now, at B2, I use this formula:
=VLOOKUP(A2,$F$2:$F$21,1,FALSE)

Then drag the formula down for column B.

The strange thing happened at B5, B10, B11, B12 and B13 as it returns #N/A (while other are working fine). I already use IF condition to check for equality of A5 and F5 for example, the result is TRUE.

Could anyone explain to me why it is working like this, please? Is it a bug from Excel?

Many thanks for help.

Linh Pham.

 

[RoryA]

It's a floating point issue. In column F, round the numbers using =ROUND(F1+0.1,1) for example.

 

[NTLH_Excel]

Hi Rory,

Thank you very much for quick reply.

I can have some ways to work around with this issue. The thing I am really concerned is why it is happened like that? Could you help me on that, please? Or it is a known bug from Excel?

Thank you again for help.

Linh Pham.

 

[RoryA]

It's to do with floating point precision. See here for more info: Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” - Microsoft 365 Blog

 

[NTLH_Excel]

Dear Rory,

It is indeed what I am looking for.

Thank you very much and I really appreciate your help.

With best regards,

Linh Pham.