Don’t use FIND/SEARCH, use this approach instead

 

Don’t use FIND/SEARCH, use this approach instead

Surprised by the article’s title?

Here’s the scenario:

You own a restaurant and you have some waiters working on 3 different shifts: morning, noon, evening.
You want to know, for a certain period of time (say: Sunday, Monday and Tuesday), how many assigned shifts did each waiter have during that period.

You might be tempted to do this:

                   Pic 1: Wrong Solution

As can be seen clearly, this is a wrong solution:
Jean does not appear 4 times in the list, but only 3.
Dan does not appear 7 times in the list, but only twice.
So, what is the root of the problem?
The name “Jean”, for example, is contained within “Jeanine”. So the FIND function finds the substring “Jean” 4 times in the list.

The name “Dan” is contained in “Dana”, “Daniel” and “Danielle”, so the FIND function fetches all the instances of the substring “Dan” within the list.

Here we can see another shortcoming of the FIND function.
If, for example, we have “Jean” twice in cell C3, the formula:

=SUM(ISNUMBER(FIND(B7,C3)*1)

Will return a wrong result: 1 instead of 2.

FIND stops short of finding all the instances of the searched string within the cell.

                   Pic 2: Wrong Solution (a single cell)

 

So, you’d  probably ask, what is the solution to this problem?
The answer is: use EXACT instead of FIND.

The formula is a bit longer but by using the EXACT function we get the exact solution.

                   Pic 3: Correct Solution

 

 

And as a “bonus”, if the name “Jean” appears twice in cell C3, the EXACT function returns the correct result, unlike the FIND function (as demonstrated above):

                   Pic 4: Correct Solution (a single cell)