Unreliable MATCH formula

When I perform a match in L23, the formula returns 3 but it should be 4. = wrong
When I perform the same match in L22, but I replace the reference cell L19 with the value in this cell (4,8) I get the right value (it returns 3 as it should be). = right

In N21 I perform L19=4,8 and it returns TRUE... but why are these formulas not returning the same value?

matchformula.png
Column M shows the formula that is behind the cells in column L.

Thank you for your insight!

Hi Solvax,

I always forget the importance of the last parameter/argument in the Match function. For exact match it must be a zero. In your function you have a "1". This means your data must be sorted to get the results you might expect. Are you looking for an exact match?

Look at the "Match Type" argument in:

http://www.excelfunctions.net/ExcelMatch.html

Hello marvin,

I am not looking for an exact match, I am looking for the same or a lower value.
So I think match type 1 should be correct?


EDIT:
To return to my first post..
To make it easier to understand, I added an excel example file.

How can F8 and F9 have different values although H8 says they should be the same..

No idea but try to round it:

=MATCH(ROUND(F6,1),A1:D1,1)

My best guess -- computer round off error due to binary arithmetic. I suspect that 6.1-2.7 -- because decimals cannot be exactly represented in binary -- is returning a value ever so slightly smaller than 3.4. I don't have Excel on this computer, but I do have QuattroPro and Open Office. In QuattroPro, =6.1-2.7-3.4 returns -4e-16.

Interestingly, Open Office Calc must do better (or guess better or use more precision or something), because I do not see the problem when I open your file in Open Office Calc. F8 and F9 both return 2 like you would expect.

bebo's formula should help. Whatever you decide to do, I suspect that this is a result of normal computer round off error, and you will need to think through some kind of round function or other to make sure that your spreadsheet correctly handles these inevitable binary arithmetic errors.

HLOOKUP gives the same result. You could do this in F6:

=ROUND(INDEX(A1:D1,1,MATCH(J3,A2:D2,1))-J2,1)

It will be to do with a floating point error.

to go further on MrShorty's response

if you set excel to precision as displayed
you will get the result you were expecting

To enable/disable this option in excel 2010 , go to File > Options > Advanced > When calculating this workbook: > “Set precision as displayed”.

Got this link (fed by my friend) to explain about this issue:

https://en.wikipedia.org/wiki/Numeri...icrosoft_Excel

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Or for those pedantic about not to rounding their data maybe :

Please Login or Register  to view this content.

as an array formula (confirm with ctrl+shift+enter)

I note similar equal items not equaling came up recently in this thread

https://www.excelforum.com/excel-for...f-anomaly.html