I want to search a cell for a list of words. I thought this would work as an array formula:

`{=FIND(<list of words I want to search for>,<cell I want to search>)}`

However, it only recognizes a match when the word I'm looking for appears in the first row in the list of terms I'm looking for in the cell I'm searching. Is it possible to create a formula that iterates over the full list? And I would prefer that it return more than just TRUE or FALSE. I am aware of how to perform a cell search for a list of terms and output TRUE or FALSE depending on whether or not each word is present in the cell. I want to know the precise word that was found or its location.

Oct 3, 2022 in Others 2,090 views

## 1 answer to this question.

If a match is detected, this will return the word, otherwise, it will return an error. The following is what I used for this example.

List of words to search for: G1:G7
Cell to search in A1

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

Enter as an array formula by pressing Ctrl+Shift+Enter.

This formula records the position of the word in the list as a positive value if it is found or as a negative value if it is not found by first searching the list of words for matches. The position of the discovered word in the list is represented by the greatest value in this array. A negative value is supplied to the INDEX() method and an error is raised if no word is found.

To return the row number of a matching word, you can use the following:

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

This also must be entered as an array formula by pressing Ctrl+Shift+Enter. It will return -1 if no match is found.

• 63,420 points

+1 vote

## Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

## How can I perform a reverse string search in Excel without using VBA?

This one is tested and does work ...READ MORE

## Excel: How to set a dropdown list cell to fetch list of strings from an API JSON response

The values you wish to display in ...READ MORE

## Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

## Excel VBA - Subscript Out of Range Error (Run Time: Error 9)

Set rangePartner = Range(Partner.Cells(2, 2), Partner.Cells(2, 2).End(xlDown)) Needs ...READ MORE

## Examples for string find in Python

you can use str.index too: >>> 'sdfasdf'.index('cc') Traceback ...READ MORE