Match error But there is a match as a fact

[rdhoy]

I have a table with several columns as shown below. The issue is that I must do a search for a number entered manually. The search must be done in a column that results from multiplying the value $ by 100. With some values, inexplicably, the result of the formula +MATCH(I;$H$4:$H $164;0 ) is #N/A although the value ACTUALLY EXISTS, in other cases the value is found.

I.E. In the first row, the value 4.44 is loaded, which is multiplied by 100, resulting in 444. Then, the value to be searched for is 444 (manually entered) and the result of the formula is #N/A, but with the formula +IF( D=C;"YES";"NO") the result is true (YES).

Why is MATCH bahaving that way when there is actually a match????

IF SOMEONE CAN HELP ME I WOULD APPRECIATE IT

A $ Value	B Quantity	C total (formula) =A*B	D manual Entry	SEARCH +match(D;$C$2:$C$164;0 )	compare
4,44	100	444	444	#N/A	YES
4,43	100	443	443	3	YES
4,52	100	452	452	#N/A	YES
4,53	100	453	453	5	YES
4,59	100	459	459	6	YES
4,59	100	459	459	6	YES
4,59	100	459	459	6	YES
4,59	100	459	444	#N/A	NO
4,54	100	454	454	10	SI
4,54	100	454	454	10	YES
4,56	100	456	456	#N/A	YES
4,58	100	458	458	13	YES
4,58	100	458	458	13	YES
4,58	100	458	458	13	YESI
4,62	100	462	462	16	YES
4,61	100	461	461	#N/A	YES
4,62	100	462	462	16	YES

 

[bebo021999]

May be try with D+0
match(D2+0;$C$2:$C$164;0 )
or
match(D2+0;$C$2:$C$164+0;0 ) (ctrl-shift-enter)
If it does not works, try to post worksheet screenshot to see if there was any clue

 

[Zot]

I did not get why your formula was written like that. This work just fine
=MATCH(D2,$C$2:$C$164,0 )

 

[alansidman]

change your formula in column C to =ROUND(A4*B4,0)

 

[Alex Blakenburg]

Didn't work doing on the fly here will redo it in Excel

 

[Alex Blakenburg]

This works in MS 365 and may not work in earlier versions, I would be interested to know if does or doesn't.

=MATCH(D2,ROUND($C$2:$C$164,0),0)

I have followed Alan in rounding to 0 but you don't need to be that aggressive. I have tried rounding using up to 15 and it seems to still work (assuming it is the floating-point arithmetic issue).
For time which is not exactly the same but similar problem I normally find around 5 decimals to be the sweet spot.

 

[rdhoy]

I did not get why your formula was written like that. This work just fine
=MATCH(D2,$C$2:$C$164,0 )

sorry i did not add the ref (2) because i thought it could confuse you, but it actually is written like this match(D2;$C$2:$C$164;0 )

 

[rdhoy]

This works in MS 365 and may not work in earlier versions, I would be interested to know if does or doesn't.

=MATCH(D2,ROUND($C$2:$C$164,0),0)

I have followed Alan in rounding to 0 but you don't need to be that aggressive. I have tried rounding using up to 15 and it seems to still work (assuming it is the floating-point arithmetic issue).
For time which is not exactly the same but similar problem I normally find around 5 decimals to be the sweet spot.

Hello thanks for your kind answer... I agree, because i already tryed using ROUND, and it actually could solve the problem. But i would like to know if i´m facing a real EXCELL BUG. That's why i used two different formulas, to be sure that MATCH wasn`t working properly, that's why i made another comparison using IF(c2=d2), which worked correctly

 

[alansidman]

Floating Point Calculation Issues in Excel

The term floating point refers to the fact that there are no constant number of digits before or after the decimal point of a number. In other words, the decimal point itself can “float”.

www.spreadsheetweb.com