Keyword Searches in Excel Spreadsheets Using Formulas

Updated on November 15, 2016

John uses research skills (Ph D) & 30 years as CEO, manager to develop reviews for time management, productivity, staff relations, business

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'

4

5

2

0

6

4

1