NOTE: To simplify the demonstration, we will pretend that the Data Validation search list is in cell D1 of the “MasterData” sheet. This will keep us from having to switch back-and-forth between sheets during the development of the solution.
When looking for all items that contain the letters “GAR”, enter the letters “GAR” in cell D1.
In cell D2, we will create a function to perform the search.
The SEARCH function has three parameters:
- “Find_Text” – this is the text or cell holding the text to be located
- “Within_Text” – this is the cell or array of cells to be searched
- “[Start_Num]” – this is an optional parameter that allows you to define the character position within the “Within_Text” parameter you want to start searching. If this parameter is undeclared, the search begins at character position 1 (far left).
Our first test will be to locate the text in cell D1 within the text located in cell A2.
We are presented with the result “1”.
This is because the letters “GAR” appears starting in the first character position counting from the left side of the data.
If we change the search criteria to the letter “M”, we are returned the number “6”, since the “M” occurs in the sixth character position of the name “Gary Miller”.
If we change the search criteria to something that does not appear in the search text, such as the letter “V”, we are presented with a #VALUE! error.
Let’s update our formula to include the full range of Customer names.
The result is quite the attention getter!
This is the power of Dynamic Arrays.
Dynamic Arrays allow us to write a single formula but return many results. In this case, we wish to locate the text in cell D1 within every item of the Customer list. The answers appear in cells D2:D32.
This is known as a “spilled array”.
If we change the search criteria to “ROB”, we see that the letters appear in four of the Customer names, yielding numbers, and #VALUE! errors for all other list items.