# MATCH function return error



## Suhari (Dec 27, 2022)

I'm having failures using the MATCH function, even for very simple cases like I've shown in the screenshot below. What actually happens to this MATCH function? Why in some cases (in yellow) it doesn't work properly but in others it gives the result as it should? I don't expect to use the ROUND function in this case. I'm using Excel 2019 for windows.





```

```


----------



## Joe4 (Dec 27, 2022)

Welcome to the Board!

It looks like you are using a named range called "Score".  
What range is this named range defined as?

Are the values you are looking up in column C calculations or hard coded (if calculated, please provide the formula)?
Are the values in your "Score" named range calculations or hard-coded (if calculated, please provide the formula)?


----------



## Suhari (Dec 27, 2022)

Joe4 said:


> Welcome to the Board!
> 
> It looks like you are using a named range called "Score".
> What range is this named range defined as?
> ...


Yes, I defined that range (in blue) as "Score" in hard-coded. In column C, for example in C2, I write "=A2+B2", in C3 "=A3+B3" and so on. In C13 to C18, I change the + operator to -.


----------



## Joe4 (Dec 27, 2022)

If you change the formula in C2 to this:

```
=ROUND(A2+B2;1)
```
and copy down for all rows, does it fix the problem?


----------



## Suhari (Dec 27, 2022)

OK, thanks, for a while I did use the ROUND function to solve the problem. However, I don't want to use the ROUND function because it is related to the dynamics of the number of digits that I will use in the reference table. Actually, my main question is why doesn't the MATCH function work on some numbers which are the result of mathematical operations whose values are quite exact, for example 0.1 + 0.2 whose result is clearly 0.3 and that value is in the reference table, while on other numbers it can function properly? What's the difference between the value 0.3 in column C in rows 5, 6 and so on to 10, so that using the MATCH function for the same lookup value, some work well but some produce errors?


Joe4 said:


> If you change the formula in C2 to this:
> 
> ```
> =ROUND(A2+B2;1)
> ...


----------



## Joe4 (Dec 27, 2022)

It is probably due to the famous "floating-point arithmetic" error in Excel.
See here more an explanation and work-arounds: Floating-point arithmetic may give inaccurate result in Excel - Office

The two most common workarounds are rounding (like I showed you), or using the "set precision as displayed" worksheet formatting option.


----------

