Forum Discussion

JRink99's avatar
JRink99
Copper Contributor
Jan 02, 2025

Searching for multiple words in an Excel spreadsheet (ugh, why is this so hard?)

I know that I can search for words in Excel using CTRL-F.    However, I have an Excel spreadsheet that I regularly need to search for roughly 40 keywords, and I need to do that on a weekly basis as the contents of that spreadsheet itself changes weekly.  I don't want to have to CTRL-F and do 40 different word searches every since time (especially since I can't remember all 40 keywords I need to search for off the top of my head).

 

Is there a way to have Excel search an entire worksheet (or even just Column "B" of that worksheet), for the following keywords (as an example)

 

Apple
Pear
Monkey
Table
Chair
Television
Microwave (I'd want it to report back finding "Microwave" even if it actually found "Microwave Oven")
Mouse
Waterfall

etc. (for all 40 words)

... and then have it tell me WHICH of those keywords it found in that worksheet?

 

I have to imagine there is some EASY way to do this with a FORMULA or a MACRO?  (of which I have basically no knowledge of how to do either) .   I'm guessing worksheet #1 could be my keywords, and worksheet #2 could be the actual spreadsheet that I'm searching, but I've no idea how to make this work. 

 

Any help here? 

  • I created a sheet named Data with some sentences in cells in several rows/columns.

    I also created a sheet named Keywords with a range of words in A2:A18 - in your real workbook, that would extend to A41 or so.

    I selected this range, clicked in the Name/Address box on the left hand side of the formula bar, typed the name Keywords and pressed Enter. This names the range with the keywords.

    In cell C2 on the Keywords sheet, I entered the formula

    =FILTER(Keywords, COUNTIF(Data!A1:Z100, "*"&Keywords&"*"), "-none-")

    This range looks at the Keywords range, and returns only the items that satisfy the condition that

    COUNTIF(Data!A1:Z100, "*"&Keywords&"*")

    returns a non-zero value. If none of the keywords satisfy the condition, "-none-" is returned.

    COUNTIF(Data!A1:Z100, "*"&Keywords&"*") looks at the range Data!A1:Z100 - you can expand or shrink this as you like. It counts the number of cells in this range that contain a keyword, plus possibly other text: "*" is a wildcard that stands for any number of characters (including none).

     

    An alternative would be to highlight the keywords that occur on the Data sheet - see the attached version. I created a Conditional Formatting rule for the Keywords range.

    • JRink99's avatar
      JRink99
      Copper Contributor

      Hello.  Thanks for your reply, but I don't know what to do with the workbook you provided once downloaded?    Edit:  I noticed the formula.   Can you explain the formula and step me through the logic of what it's doing so I understand it better?

      • I created a sheet named Data with some sentences in cells in several rows/columns.

        I also created a sheet named Keywords with a range of words in A2:A18 - in your real workbook, that would extend to A41 or so.

        I selected this range, clicked in the Name/Address box on the left hand side of the formula bar, typed the name Keywords and pressed Enter. This names the range with the keywords.

        In cell C2 on the Keywords sheet, I entered the formula

        =FILTER(Keywords, COUNTIF(Data!A1:Z100, "*"&Keywords&"*"), "-none-")

        This range looks at the Keywords range, and returns only the items that satisfy the condition that

        COUNTIF(Data!A1:Z100, "*"&Keywords&"*")

        returns a non-zero value. If none of the keywords satisfy the condition, "-none-" is returned.

        COUNTIF(Data!A1:Z100, "*"&Keywords&"*") looks at the range Data!A1:Z100 - you can expand or shrink this as you like. It counts the number of cells in this range that contain a keyword, plus possibly other text: "*" is a wildcard that stands for any number of characters (including none).

         

        An alternative would be to highlight the keywords that occur on the Data sheet - see the attached version. I created a Conditional Formatting rule for the Keywords range.

Resources