How to Do Keyword Searches in Excel Spreadsheets Using Formulas

Searching for lists of keywords or phrases is a common task in Excel. While it can be done using the 'Filter' tool and the 'contains' option, these searches can only be done one at a time. Using a formula is a better option, especially when you can simultaneously search for two or more words at once, or a list of words.

Most keyword searches are not 'smart' in the sense of eliminating unwanted 'hits'. For example, conducting a search for 'pest' will find 'pesticide'. Similarly a search for 'dog' will find 'hotdog' and 'dogmatic'.

This article describes how to conduct 'smart' keyword searches in Excel, using formulas in ways that avoid irrelevant 'hits'. 

Using the Excel Filter Tool to Find Keyword Matches

The Filter Tool offers a range of options. The most useful is the 'contains' option, which selects rows for a specified field that contains the entered keyword or phrase. The 'Filter' example in the table shows the outcome for a search of 'cat' in the column 'Animals'. This shows that the search literally does a 'contains' search, and may produce some undesirable outcomes such as finding 'category', as shown in the table.

You cannot include a 'space' before or after 'cat', such as '_cat_' or '_cat_' to narrow the search. You would also need a separate search for '_cats_'. Also fhe number of 'AND' and 'OR' options available in the filter tool is limited to 2 keywords.

Using an Excel Formula to Find Keyword Matches

► For a single search term use this formula:

=IF(ISNUMBER(SEARCH("cat",B2)),"found cat","")

This searches in B2 and adds the label 'found cat' for each find.

One advantage of using a formula is that you can add 'spaces' to restrict the search.

For example using "* cat *", with a space either side of 'cat', will only find exact matched for 'cat'.

► For a multiple search terms (OR or AND searches ) - use these formulas:

For two keywords

=IF(OR(ISNUMBER(SEARCH("cat",B2)),ISNUMBER(SEARCH("dog",C3))),"cat or dog","") - B2 is the column searched.

For four keywords, this is the formula:

=IF(OR(ISNUMBER(SEARCH("cat",C3)),ISNUMBER(SEARCH("pig",C3)),ISNUMBER(SEARCH("dog",C3)),ISNUMBER(SEARCH("horse",C3))),"found cat, pig, dog or horse","")

You can add more keywords to extend the search, but the list option described below is more veratile.

► For searching for words in a column array or list, use this formula:

=IF(ISNUMBER(LOOKUP(9.99E+307,SEARCH($C$2:$C$7,B2))),"found","")

Where A2 to A7 is the list of words searched in column B (see the table).

This formula produces the selection shown in the "List Search" Column for the 'List of Keywords' shown in the first column in the table.

Note: you can include a space before and after the word in the 'List of Keywords' to be found (first column) and this will restrict the search to exact matches.

For example if you enter ' cat ' with a space before and after 'cat' the search will not find 'category' and any other words with another letter before or after 'cat'.

BUT you will have to include '_cats_' and "_cat-_" if you want to find the plural 'cats' and word with 'cat' plus a hyphen.

Examples of How to Conduct Keyword Searches in Excel

List of Keywords
Animals
Filter
Formula 'cat'
cat' + space
OR Search
List Search
cat
cat
contains 'cat'
found 'cat'
found 'cat_'
cat OR dog
found
cats
dog
      
cat OR dog
found
dog
bear
          
dogs
horse
          
pig
category
contains 'cat'
found 'cat'
      
pigs
cats
contains 'cat'
found 'cat'
      
 
pigs
        
found
 
horses
          
 
man
          
 
dogs
        
found
 
women
          
 
woman
          
 
cat, dog, bear, horse, category, cats, dogs, horses, man, do, women, woman,
contains 'cat'
found 'cat'
    
found
 
catch
contains 'cat'
found 'cat'