Search code examples
excelsearchexcel-formulacellworksheet-function

Formula that will search cells in table to find a word

I'm looking for a formula that will allow me to look for a value in a table and return any information if the value exists. Either True/False or a number where it starts (like search formula).

The problem is that I will be looking for a word Apple and in the table there might be like:

Column A

  • Orange
  • Apple, grapefruit
  • Peach, tomato, carrot
  • Potato
  • etc.

So everything is in one column but there might be more than one value in one cell. The values do not repeat. So in the table there will be only one Apple.

Is there any way to do this with a formula?

Solution

  • MATCH allows wildcards, so: 

    =MATCH("*Apple*",A:A,0)

    should return the row number in which Apple is in a cell in ColumnA. If more than one instance will return only the first.