match formula not finding all values in a Matrix

[Dan in Germany]

Hi,

I'm having a similar problem with the match formula as in this thread:
http://www.mrexcel.com/forum/showthread.php?t=544628&highlight=match+formula+format

however, the fix presented here doesn't help with 100% of the time.

background:

I copied and pasted an access table into excel. In the excel table I have information on different soil horizons, including one column for the top of the soil horizon and one column for the bottom depth of the soil horizon (in meters). I am using the match formula (in the german system its called "Vergleich") to find the top and bottom borders in a long matrix, about 4000 rows long. This long matrix I calculated myself in excel and every row represents 0,5mm, and in this matrix the top and bottom soil horizon borders are found.

Unfortunately, the match formula does not always find the corresponding value in the matrix although the value is there! I tried the text to column trick in the other thread and that helped eliminate the majority of the problems, however, the formula still can not find all the values. When I find the value in the matrix for which I'm searching and overwrite that value by hand and hit enter, then the match function will recognize the value and return its position in the matrix.

can anyone give me some advice?

Thanks in advance!

 

[Andrew Poulsom]

Maybe this will help you:

http://www.contextures.com/xlFunctions03.html#Trouble

 

[Dan in Germany]

Thank you for the link! The text in columns command help but only partially, even after using it on the search AND matrix Columns (both of these had issues...) However, I did find out what the problem was.

The matrix column I had calculated was about 4000 rows long, I simply added 0.0005+0.0005 untill I got to 2.000 (I had to sum one half of a millimeter untill I got to 2 meters). I typed this formula into cell A3 and cell A2 was "0"

=A2+0.0005

then I draged the formula till I got till 2.000. Apparently, there were some rounding errors from excel. For example, 0.5995 + 0.0005 was not equal to 0.6000. Instead it was equal to 0,599999999999989! when I searched vor the value 0.6000 of course it wasn't in the matrix to be found and since the cells in excel automatically rounded to four places I didn't see the rounding error. I used the formula "ROUND" to round the results to four decimal places, then the MATCH function worked!

why would such a rounding error occurr with Excel and what can I do to avoid this in the future? That really caused a big headach!

thanks for your reply!

 

[Andrew Poulsom]

Look here:

http://support.microsoft.com/kb/214118