Vlookup/match is not working even the values are matched - Microsoft Community

i have 2 list same numbers , source of data same. however, 1 list after sumif, vlookup , match functions not seem work.

weird thing tested values "=" , match.

i understand has floating-point decimal can please explain why "=" works not indexing functions. 

https://1drv.ms/f/s!aqnjw6k-rdno3wjgk80xqi97dkqz

this due rounding errors in floating-point arithmetic used in excel because numbers stored in binary format. many combinations of arithmetic operations on floating-point numbers in microsoft excel may produce results appear incorrect small amounts. you may use round function overcome this.

please refer below link details.

https://support.microsoft.com/en-nz/help/214118/how-to-correct-rounding-errors-in-floating-point-arithmetic

if use below formula in cell d2, correct result:

=vlookup(round(a2,10),b!b:b,1,false)

regards,

amit tandon

www.globaliconnect.com

Office / Excel / Windows 10 / Office 2016