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

Due to the complexity and ambitiousness of the requirement, I chose google spreadsheet initially 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 certain column.

## Failed solution

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

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

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

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

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

I guess that the reason of this weird behavior is the fuzzy matching functionality of the `countif` function, as it is also able to check for pattern with asterisk, number order (>=, =, <=, ...).

## Lesson learnt

I did more debugging by removing digits one by one. Then I figured out that when the being 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 make sure that all other functions do not compare value in the same manner, I also checked other places where I use `vlookup`.
Fortunately, `vlookup` does not use fuzzy matching. Thus, 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 one wants obtain statistical values from data.
The lesson learnt here is that never use `countif` when the criterion parameter (the second parameter) is dynamic. You should use this function only when you know what is the value of the criterion exactly (or statically).

## Solution

Eventually, I chose `MATCH` function 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.