How to Perform Text Lookup in a Column with Excel Formula

Sometimes, you may want to check if a column in Excel contains any of the words in a cell. For example, you may have a list of customer names in one column and a list of keywords in another cell, and you want to see if any of the names match any of the keywords. This can be useful for filtering, sorting, or highlighting data based on certain criteria.

There are different ways to achieve this task in Excel, depending on your needs and preferences. In this article, we will show you three methods to look up if a whole column contains any of the words in a cell in Excel formula:

• Method 1: Using the SEARCH and ISNUMBER functions
• Method 2: Using the COUNTIF and wildcards
• Method 3: Using the TEXTSPLIT and XMATCH functions

We will explain the basic theory behind each method, the steps to apply them, and a scenario to give a detailed example with real numbers. We will also use an Excel table to illustrate the results.

Method 1: Using the SEARCH and ISNUMBER functions

The SEARCH function is designed to look inside a text string for a specific substring. If SEARCH finds the substring, it returns the position of the substring in the text as a number. If the substring is not found, SEARCH returns a #VALUE error. For example:

= SEARCH ("p","apple") // returns 2 = SEARCH ("z","apple") // returns #VALUE!

The ISNUMBER function returns TRUE for numeric values and FALSE for anything else. So, if SEARCH finds the substring, it returns the position as a number, and ISNUMBER returns TRUE. If SEARCH doesn’t find the substring, it returns an error, which causes the ISNUMBER to return FALSE. For example:

= ISNUMBER ( SEARCH ("p","apple")) // returns TRUE = ISNUMBER ( SEARCH ("z","apple")) // returns FALSE

To check if a whole column contains any of the words in a cell, we can use the following formula:

= ISNUMBER ( SEARCH (cell,column))

Where cell is the cell that contains the words to look for, and column is the column that contains the text to search. This formula will return TRUE if any of the words in the cell are found in the column, and FALSE if not.

Note that the SEARCH function is not case-sensitive, so it will match upper and lower case letters. If you want to perform a case-sensitive search, you can use the FIND function instead of the SEARCH function.

Steps

To apply this method, follow these steps:

1. Enter the words to look for in a cell, separated by commas. For example, enter “apple, banana, cherry” in cell A1.
2. Enter the text to search in a column, starting from row 2. For example, enter the following customer names in column B, from B2 to B6:

3. In another column, enter the formula = ISNUMBER ( SEARCH (A1,B2)) in the first cell, and drag it down to fill the rest of the cells. For example, enter the formula in cell C2, and drag it down to C6. You should see the following results:

The formula returns TRUE for the first three customers, because their names contain one of the words in cell A1. The formula returns FALSE for the last two customers, because their names do not contain any of the words in cell A1.

Method 2: Using the COUNTIF and wildcards

The COUNTIF function counts the number of cells in a range that meet a certain criterion. The criterion can be a number, a text, a cell reference, or an expression. For example:

= COUNTIF (A2:A6,">10") // counts the number of cells in A2:A6 that are greater than 10 = COUNTIF (B2:B6,"apple") // counts the number of cells in B2:B6 that are equal to "apple" = COUNTIF (C2:C6,D1) // counts the number of cells in C2:C6 that are equal to the value in D1 = COUNTIF (D2:D6,"*y") // counts the number of cells in D2:D6 that end with "y"

The last example uses a wildcard, which is a special character that can match any character or sequence of characters. The asterisk (*) wildcard matches zero or more characters. The question mark (?) wildcard matches any one character. For example:

= COUNTIF (E2:E6,"x?z") // counts the number of cells in E2:E6 that have "x" followed by any one character followed by "z" = COUNTIF (F2:F6,"*ing") // counts the number of cells in F2:F6 that end with "ing"

To check if a whole column contains any of the words in a cell, we can use the following formula:

= COUNTIF (column,"*" & cell & "*") > 0

Where cell is the cell that contains the words to look for, and column is the column that contains the text to search. This formula will return TRUE if any of the words in the cell are found in the column, and FALSE if not.

Note that the COUNTIF function is not case-sensitive, so it will match upper and lower case letters. If you want to perform a case-sensitive search, you can use the COUNTIFS function instead of the COUNTIF function, and add a condition for the EXACT function. For example:

= COUNTIFS (column,EXACT (cell,column)) > 0

Steps

To apply this method, follow these steps:

1. Enter the words to look for in a cell, separated by commas. For example, enter “apple, banana, cherry” in cell A1.
2. Enter the text to search in a column, starting from row 2. For example, enter the following customer names in column B, from B2 to B6:

3. In another column, enter the formula = COUNTIF (B2:B6,"*" & A1 & "*") > 0 in the first cell, and drag it down to fill the rest of the cells. For example, enter the formula in cell C2, and drag it down to C6. You should see the following results:

The formula returns TRUE for the first three customers, because their names contain one of the words in cell A1. The formula returns FALSE for the last two customers, because their names do not contain any of the words in cell A1.

Method 3: Using the TEXTSPLIT and XMATCH functions

The TEXTSPLIT function is a new function in Excel that can split a text string into an array of substrings, based on one or more delimiters. The delimiters can be characters, strings, or regular expressions. For example:

= TEXTSPLIT ("apple, banana, cherry",", ") // returns {"apple","banana","cherry"} = TEXTSPLIT ("xyz",".") // returns {"x","y","z"} = TEXTSPLIT ("123-456-7890","-") // returns {"123","456","7890"}

The XMATCH function is another new function in Excel that can return the relative position of an item in an array or range that matches a specified value. The XMATCH function can also perform a case-sensitive or wildcard match, and return the position of the first or last match. For example:

= XMATCH ("apple",{"apple","banana","cherry"}) // returns 1 = XMATCH ("Apple",{"apple","banana","cherry"},0,0,TRUE) // returns 1 (case-sensitive) = XMATCH ("*y",{"xyz","xbz","xyy"},2) // returns 3 (wildcard) = XMATCH ("cherry",{"apple","banana","cherry"},0,1,-1) // returns 3 (last match)

To check if a whole column contains any of the words in a cell, we can use the following formula:

= COUNT (XMATCH (TEXTSPLIT (cell,delimiters),column)) > 0

Where cell is the cell that contains the words to look for, delimiters are the characters or strings that separate the words in the cell, and column is the column that contains the text to search. This formula will return TRUE if any of the words in the cell are found in the column, and FALSE if not.

Note that the TEXTSPLIT and XMATCH functions are only available in Excel 365 and Excel Online. If you have an older version of Excel, you may not be able to use this method.

Steps

To apply this method, follow these steps:

1. Enter the words to look for in a cell, separated by commas. For example, enter “apple, banana, cherry” in cell A1.

2. Enter the text to search in a column, starting from row 2. For example, enter the following customer names in column B, from B2 to B6:

3. In another column, enter the formula = COUNT (XMATCH (TEXTSPLIT (A1,", "),B2:B6)) > 0 in the first cell, and drag it down to fill the rest of the cells. For example, enter the formula in cell C2, and drag it down to C6. You should see the following results:

The formula returns TRUE for the first three customers, because their names contain one of the words in cell A1. The formula returns FALSE for the last two customers, because their names do not contain any of the words in cell A1.