Recently, I was asked to build a shopping order system for my sister.

Due to the complexity and ambitiousness of the requirement, I initially chose the google spreadsheet to build a simple system before starting with a web-based system.

Then I got to a case that I need to make a cell validation, which ensures there is no ID duplicated in a particular column.

## Failed solution

This stack exchange question appeared first when I googled.
Based on the accepted answer, I got the first solution by using the `countif` function. It works with the following expressions.

The solution works perfectly until my sister told me that the validation continuously fails even when no duplicated ID entered.

After debugging, I found out that the `countif` function does not work as expected in some cases, such as in the following example.

``````  |          A          | B |        C
------------------------------------------------
1 | 562572814105416318  | 2 | =COUNTIF(A1,A1:A2)
2 | 562572814105416000  | 1 | =COUNTIF(A2,A1:A2)
------------------------------------------------``````

Wow. Surprised!!. The `countif` function returns results that are totally different from what I expected.

All I can do is guessing.
I think that this weird behavior is the fuzzy matching functionality of the `countif` function due to its ability to check for a pattern with asterisks, number value comparisons (>=, =, <=, ...).

## Lesson learned

I did more debugging by removing digits one by one. Then I figured out that when the compared numbers have more than 15 digits, the `countif` function rounds all them off.

``````  |          A             | B |        C
---------------------------------------------------
1 | 1.000.000.000.000.000  | 2 | =COUNTIF(A1,A1:A2)
2 | 1.000.000.000.000.001  | 1 | =COUNTIF(A2,A1:A2)
---------------------------------------------------``````

To ensure that all other functions do not compare the value in the same manner, I also checked other places where I use the `vlookup`.
Fortunately, the `vlookup` does not use fuzzy matching. Therefore, values are compared exactly.

``````  |          A             |  B  |  C  |            D
-------------------------------------------------------------------
1 | 1.000.000.000.000.000  | foo | foo | =VLOOKUP(A1,A1:B2,2,FALSE)
2 | 1.000.000.000.000.001  | bar | bar | =VLOOKUP(A2,A1:B2,2,FALSE)
-------------------------------------------------------------------``````

Of course, `countif` is very useful in many cases when we want to obtain various statistics from the data.
The lesson learned here is that you should never use `countif` when the criterion parameter (the second parameter) is dynamic. It is safe to use this function only when you know what the value of the criterion exactly (or statically) is.

## Solution

Eventually, I chose `MATCH` function as the final solution because of its true equality compare function.

This google spread sheet includes all the test formulas. You can open to check by yourself or make a copy and play with more formulas.