Match function error

[honeybhattal]

https://drive.google.com/open?id=0B69LhOObJF1XQ2ViQVJEczducW8&authuser=0


Here is a link to my simple excel file.

There is a problem in using the match function. You can edit the ranges filled with light yellow to get the results filled with sky blue. The problem is with cell F9. Even when the value in E9 is the same (F9 has a match function which matches value in E9) for different values in E11, F9 sometimes fail to deliver the value and simply puts a 0(Iserror function). I extracted the formula from these cells to K17 and K18. I want to know why is this #N/A error occuring. If i replace the cell K17 with the same number as displayed, in K17 (as obtained from the formula in K17), the formula in K18 works.

I am unable to catch the issue. Somebody please help.

 

[GlennUK]

Your links says I have to ask for permission to access the file ... so I've clicked Request Permission.

 

[honeybhattal]

Your links says I have to ask for permission to access the file ... so I've clicked Request Permission.

I have changed my settings, you can now access the file, sorry for the inconvenience.

 

[GlennUK]

It's strange ... it seems like a real number mismatch, which can sometimes happen in Excel ... it all starts working if you put a ROUND function around the formula in E9:

=ROUND(IF(AND(ROUNDUP(E11,1)>1.5,ROUNDUP(E11,1)<1.75),1.5,IF(AND(ROUNDUP(E11,1)>1.75,ROUNDUP(E11,1)<2),1.75,ROUNDUP(E11,1))),3)

 

[honeybhattal]

Thanks, Glenn. Your help much solved the problem for now but i would like to know how to detect this kind of problems and how to find a solution. Or in other words, how did you know it was a real number mismatch?? is this a bug in excel??

 

[GlennUK]

I suspected it could be solved by putting a ROUND around it because I've seen this kind of problem so often ... if you are matching real numbers ( not integers ) exactly, this is one of the things that happens. It's just one of the things to consider when doing real number comparisons.