Search by Keywords – Excel & Google Sheets

This tutorial will demonstrate how to search by keywords in Excel and Google Sheets.

search by keywords Main Function

Search by Keywords

To categorize text cells based on the keywords they contain, you can use the SEARCH, ISNUMBER, MATCH, and INDEX Functions combined.

search by keywords 01

Note: This is an array formula. When using Excel 2019 and earlier, you must enter the array formula by pressing CTRL + SHIFT + ENTER (instead of ENTER), telling Excel that the formula in an array formula. You’ll know it’s an array formula by the curly brackets that appear around the formula (see top image). In later versions of Excel and Excel 365, you can simply press ENTER instead.

Let’s see how this formula works.

SEARCH Function

Typically, the  SEARCH Function looks for a string of text inside a cell value, returning the position where the text is found.

Search

 

However, if you use an array formula, and enter an array of values to search for, the SEARCH Function will return an array of matches.

Search Array

As shown above, For cell B3 (“toyota rav”), it will return an array like this:

meaning that it found only one of the keywords (“rav”) in the string, at position 8.

ISNUMBER Function

The ISNUMBER Function translates the array given by the SEARCH Function to TRUE and FALSE values.

ISNUMBER

 

MATCH Function

With the MATCH Function, we find the position of the TRUE value in our ISNUMBER array from above.

search by keywords 02

For “toyota rav,” the TRUE is the second value in the array.

INDEX Function

Finally, we use the result of the MATCH Function to determine which Category row we need with the INDEX Function.

search by keywords 03

The second row of the Categories list is “Crossover & suv,” so that’s the matching Category for “toyota rav.”

Replacing “C3” with the MATCH expression brings us back to our original formula:

 

Reminder: This is an array formula. When using Excel 2019 and earlier, you must enter the array formula by pressing CTRL + SHIFT + ENTER (instead of ENTER), telling Excel that the formula in an array formula. You’ll know it’s an array formula by the curly brackets that appear around the formula (see top image). In later versions of Excel and Excel 365, you can simply press ENTER instead.

Search by Keywords in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

search by keywords Google Function

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!