Excel Array Formulas: Searching for a List of Words in a String and Returning the Match

Introduction

In Excel data processing, it is often necessary to check if a cell contains any word from a specific list. While simple boolean returns (e.g., TRUE or FALSE) can meet some needs, in many scenarios, users desire more detailed information, such as the specific matched word or its position in the list. Based on a common problem, this article explores how to achieve this functionality using array formulas and deeply analyzes its core principles.

Problem Background and Challenges

The user initially attempted to use the array formula {=FIND(<list of words I want to search for>,<cell I want to search>)} to search for a list of words in a cell but found it only worked when the first word in the list matched. This is because the FIND function in an array context defaults to returning only the first match and cannot traverse the entire list. Therefore, a more robust method is needed to scan all words and return matching information.

Core Solution: Returning the Matched Word

The best answer provides an efficient array formula that returns the matched word or an error if no match is found. Assuming the word list is in range G1:G7 and the cell to search is A1, the formula is:

=INDEX(G1:G7,MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*(ROW(G1:G7)-ROW(G1)+1)))

This formula must be entered as an array formula by pressing Ctrl+Shift+Enter. Its working principle can be broken down into several steps:

1. Using the FIND Function to Check for Matches: FIND(G1:G7,A1) attempts to find each word from G1:G7 in A1. If found, it returns the starting position of the word in A1 (a number); if not found, it returns the error value #VALUE!.
2. Handling Error Values: ISERROR(FIND(G1:G7,A1)) converts error values to TRUE and non-error values to FALSE. Then, IF(ISERROR(...),-1,1) maps TRUE to -1 and FALSE to 1, creating an array where matched words correspond to 1 and non-matched words to -1.
3. Calculating Position Indexes: ROW(G1:G7)-ROW(G1)+1 generates the relative position of each word in the list (e.g., G1 as 1, G2 as 2, etc.). Multiplying this with the array from the previous step yields a new array: positions of matched words as positive numbers and non-matched words as negative numbers.
4. Determining the Maximum Position: MAX(...) extracts the maximum value from this array. Since positive numbers indicate the position of matched words, the maximum value is the position of the matched word in the list. If no word is matched, all values are negative, and MAX returns -1 (or a similar negative value).
5. Returning the Matched Word: INDEX(G1:G7, ...) uses the position index from the previous step to retrieve the matched word from the word list. If the position is negative (e.g., -1), the INDEX function throws an error, indicating no match.

For example, if G1:G7 contains ["apple", "banana", "cherry"] and A1 contains "I like banana", the formula returns "banana". If A1 contains "fruit", it returns an error.

Extended Application: Returning the Row Number of the Matched Word

Sometimes users may be more interested in the row number of the matched word in the list rather than the word itself. For this, the formula can be modified:

=MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*ROW(G1:G7))

Also entered as an array formula, this returns the actual row number of the matched word (e.g., if the matched word is in G3, it returns 3). If no word is matched, it returns -1. This is achieved by directly using the ROW function to calculate row numbers, omitting the INDEX step and simplifying the output.

Supplementary Method: Boolean Return

As a reference, other answers provide simplified methods that return TRUE/FALSE, suitable for scenarios where only confirmation of a match is needed. For example:

=OR(IF(ISNUMBER(SEARCH($G$1:$G$7,A1)),TRUE,FALSE))

Or a case-sensitive version:

=OR(IF(ISNUMBER(FIND($G$1:$G$7,A1)),TRUE,FALSE))

These formulas use the SEARCH (case-insensitive) or FIND (case-sensitive) functions, combined with ISNUMBER and OR, to return boolean values. They also require array formula entry but have simpler logic, ideal for quick checks.

Technical Details and Considerations

• Array Formula Entry: All the above formulas must be entered as array formulas by pressing Ctrl+Shift+Enter. In Excel, this adds curly braces {} around the formula (but they should not be typed manually).
• Error Handling: The core solution gracefully handles no-match cases via ISERROR and IF, returning an error or -1 to avoid misleading outputs.
• Performance Considerations: For large word lists, array formulas may calculate slowly as they involve multiple array operations. It is advisable to optimize range sizes or use dynamic array features if available.
• Semantic Escape Example: When explaining HTML tags, such as discussing the text representation of the <br> tag, angle brackets need to be escaped to prevent parsing errors. For instance, in code, print("<T>") should be written as print("<T>") to ensure correct display.

Conclusion

By combining FIND, INDEX, MAX, IF, and ISERROR functions, Excel users can construct powerful array formulas to search for a list of words and return the matched word or its position. This method goes beyond simple boolean checks, offering richer data analysis capabilities. In practical applications, choose to return the word, row number, or boolean value based on needs, and pay attention to array formula entry methods and performance impacts. The examples and step-by-step explanations in this article aim to help readers deeply understand these techniques and enhance their Excel data processing skills.