SEARCH function and FIND function in Microsoft Excel

There are two very similar functions in Excel to search for data in cells that match parameters you dictate: SEARCH and FIND. In fact, they are so similar that one wonders why there are two separate functions that return virtually identical results and are identical in the construct of the formula. This article will discuss one fundamental difference.

SEARCH Introduction

The SEARCH function is a way of finding a character or string of characters in another cell and returns the value associated with the starting location. In other words, if you are trying to find where a character is within the cell that contains a word, phrase, or other information, you can use the SEARCH function. The format for this function is:

=SEARCH(“find_text”,”within_text”,start_num).

For example, if the word “Alphabet” was in cell C2 and your model needed the position of the letter “a” in that cell, you would use the formula =SEARCH(“a”,C2,1) and the result would be 1. To continue this simplified example, if you search for the position of “b” in the word, the formula would be =SEARCH(“b”,C2,1) and the result would be 6. You can also use string search. For example, if cell F2 contains 1023-#555-A123, the formula =SEARCH(“A12”,F2,1) would return the 11 as the answer.

FIND Introduction

The FIND function is another way to find a character or string of characters in another cell, and it returns the value associated with the starting location, just like the SEARCH function. The format for this function is:

=FIND(“find_text”,”within_text”,start_num).

Using the same example as before, the position of the letter “a” in cell C2 would be found with =FIND(“a”,C2,1) and the result would be 1. Finding “b” in cell C2 would be reached where =FIND(“b”,C2,1) returns the number 6. Finally, further down the similarity path, if cell F2 contains 1023-#555-A123 (as before), the formula =FIND(“A12”,F2,1) would return the 11 as the answer. As you can see, up to this point, you’ve gotten the same results with either method.

Hint: You probably quickly realized that in cell C2 there are two a’s in the word. By specifying the starting point in each of the formulas as 1, we include the first occurrence of the letter “a”. If we had to choose the next instance, we could just set the “start_num” part of the formula to 2, which would skip the first instance of the letter and get an answer of 5.

main differences

The main difference between the SEARCH function and the FIND function is that FIND is case sensitive and SEARCH is not. So if you use the formula =SEARCH(“A”,C2,1) (note the capital “A”), the result would still be 1 as in the previous case. If you use the formula =FIND(“A”,C2 ,1), you would use #VALUE! receive. FIND is case sensitive and there is no “A” in the word “alphabet”.

Another difference is that SEARCH allows wildcards to be used, while FIND does not. In this context, a question mark searches for an exact phrase or string of characters in a cell, and an asterisk searches for the beginning of the string immediately before the asterisk. For example, the formula =SEARCH(“a?p”,C2,1) in our alphabet example would return an answer of 1 because it searches for an exact grouping of the letter “a” with anything next to it, with a “p” immediately after it. Since this is at the beginning of the word, the returned value is 1. Continuing with the alphabet example, the formula =SEARCH(“h*t”,C2,1) would return a value of 4. In this case, the “*” wildcard can represent any number of characters between “h” and “t” as long as a string begins and ends with the two letters you use in the formula. If the formula was =SEARCH(“h*q”,C2,1) you would get #VALUE! receive.

In short, these two formulas are very similar, and unless you need confirmation of an exact character or string, you’d probably be more likely to choose to use SEARCH. Instances where this may not be the case may include searches for specific SKUs or employee names. In my experience, SEARCH has been more helpful in certain financial modeling exercises, but it’s helpful to understand the differences in usage and results when working through your own modeling projects.