There are two very similar functions in Excel to find data within cells that match the parameters you dictate: SEARCH and FIND. They are so similar, in fact, that one wonders why they have two separate functions that perform virtually the same results and are identical in formula construction. This article will discuss the only basic difference.

Introduction

The LOOKUP function is a way to find a character or string within another cell and will return the value associated with the starting place. In other words, if you’re trying to find out where a character is within a cell that contains a word, sentence, or other piece of information, you can use the LOOKUP function. The format of this function is:

=LOOKUP(“search_text”,”inside_text”,start_num).

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

FINDChannel Introduction

The FIND function is another way to find a character or string within another cell and will return the value associated with the starting place, just like the SEARCH function. The format of this function is:

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

Using the same example as before, the location of the letter “a” in cell C2 would be discovered using =FIND(“a”,C2,1), and the result would be 1. Finding “b” in cell C2 would be =FIND(“b”,C2,1), resulting in the number 6. Finally, continuing down the similarity path, if cell F2 contains 1023-#555-A123 (as before), the formula =FIND(” A12″,F2,1) would return 11 as the answer. As you can see, up to this point, both methods would give you the same results.

Note: You probably quickly recognized that there are two a’s in the word located in cell C2. By setting the starting point in each of the formulas to 1, we will take the first instance of the letter “a”. If we were to choose the next instance, we could simply make the “start_num” part of the formula 2, thus skipping the first instance of the letter and resulting in an answer of 5.

Main differences

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

Another difference is that SEARCH allows the use of wildcards while FIND does not. In this context, a question mark will match an exact phrase or string of characters in a cell, and an asterisk will match the start of the string just before the asterisk. For example, the formula =LOOKUP(“a?p”,C2,1) in our alphabetic example would return an answer of 1, since it is looking for an exact grouping of the letter “a” with anything next to it with a ” p” immediately after. Since it is at the beginning of the word, the return value is 1. Continuing with the alphabet example, the formula =LOOKUP(“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 there is a string that begins and ends with the two letters you use in the formula. If the formula were =LOOKUP(“h*q”,C2,1), you would get #VALUE!.

In short, these two formulas are very similar, and unless you need confirmation of an exact character or string of characters, you are likely to be wrong using SEARCH. Cases where this may not be the case may involve searches involving specific SKUs or employee names. In my experience, SEARCH has been most useful in specific financial modeling exercises, but it is useful for understanding the differences in usage and results as you work on your own modeling projects.