Search by part of the name

Many years ago I worked for ECOFINANCE company where, at my time, I wrote an Excel invoicing application. It seems to me that it was quite easy to use. Partly because it was enough to enter part of the name of the code or product to find the desired item. The seller would then only select the quantity and packaging and could quickly continue to enter items until the document is completed. Today this can be achieved much easier, without any VBA programming …

How do we find an item by entering part of its name, and then put the search results in a drop-down list?

First, it is necessary to have a worksheet with information about the items (Items) such as name, packaging, classification, prices, etc. Then in the second worksheet, which we use for search (Search), we specify one cell where we need to enter the query parameters (in our example it is Search!A3).

First of all, let’s generate data in the Items worksheet to fill in the drop-down list. If we enter a function:


It allows us to search the column containing item names by a given term (Search!A3). If the function finds the term it returns an integer number indicating the position where it is located within a string; otherwise – it returns an error. Therefore, we need to check if an error has occurred by nesting the SEARCH function within IFERROR:


As a result we still have an integer value, if the term was found, and a zero if not. Now we only need to use FILTER function to filter all the items according to the formula result, where values are greater than zero:


Now all we have to do is use Data Validation and set the list to fill with values ​​from a dynamically generated array. In the Source field of the Data Validation window the formula should be entered:


The # symbol indicates that we are retrieving data from a dynamically generated string. And now, when we come back to Search worksheet, if we enter the term “Jelen” in A3 (popular brand of Serbian beer) the list will be filled with all items that, in their name, have the word „Jelen“.