Forum Discussion
Searching for multiple words in an Excel spreadsheet (ugh, why is this so hard?)
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.
See the attached workbook.
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.
Thanks for this info.
I couldn't figure out how to EXTEND the Keyword range (for example, if I wanted 100 Keywords), but noticed if I went to Formulas->Name Manage, I could find EDIT the Keywords in there and change the range. I just couldn't figure out how to extend the Keywords with the main formula or mouse clicks, etc, lol. Is editing it via Formulas->Name Manage, the right way or is that just another way of doing it?
