Match #NA Error

[brspain]

I am using Excel 2007 and have replicated this error in Excel 2013. On three separate computers in the office, from workbooks made from scratch.

So simplified:

Start with a 1x2 table with value 1.14 and a corresponding value of whatever we want to lookup.

Create a cell as a lookup value that calculates 1.14. e.g. =1+14*.01

Create an index match to lookup this value based on our calculated value.

=Index(1x2Table, Match (1+14*.01, Col1ofTable, 0), 2 )

Returns #NA in both versions of Excel. 

I can change the value of 1.14 to 1.13 or 1.15 or whatever and lookup that value based on a calculation and the function returns whatever we want to lookup. Vlookup seems to work fine for all the values as well.

What is going on?

And out of shear curiosity; further tinkering revealed that several multiples of 1.14 and corresponding calculated lookup values also returned the same #NA error. e.g. 2.28

Cells formatted as number with 2 decimals or general, doesn't seem to matter, but copy pasting the value from the calculation does clear the error and return whatever we want to lookup.

[#2]

@brspain 

---

What is going on?

---

It's just a common floating point error.

Put your calculations inside a round function and it should work.

Explanation (or rant?) by Tom Scott

[#3]

Yeah, I should have mentioned that I did that and that fixed it, but I guess my question was more along the lines of why does that seem to happen with 1.14 and not 1.13 or any of my other values.

@Detlef Lewin 

[#4]

Okay, so I watched the linked video, and again the error is along the lines of what I was thinking, but I guess I was under the impression that Excel's Cell Formatting fixed this type of error. Does it not? And why does Vlookup work where Match fails?

@Detlef Lewin 

[#5]

@brspain 

I found that, provided the calculation process is identical, the discretisation error will be the same and the match will work.  Thus 

=1+14*0.01 finds 

=1+14*0.01

but not

1.14

… and conversely.

For any comparison, the numbers should be integers, not floating point.

You might be interested to know that

1+14*0.015625

and

1.21875

work interchangeably because dividing by factors of two does not generate rounding error.

[#6]

@brspain 

VLOOKUP works if you skip optional 4th parameter, i.e. use approximate search. With it equal to TRUE it returns an error. 

Cell formatting is only formatting, it doesn't affect binary representation of the number .