true or false if columns contain certain words

[pgds]

Heya guys

I am stuck with a small problem.

I have a spreadsheet with a few rows that contain data.

I want to pick out the rows that contain certain keywords.

So for instance column A and B contain free text, I want to pick out the rows that contain certain words (example: Gunslinger, assassin, tank)
I want to have a formula that will update another column with a 0 if no matches are found or with a 1 if there is a match on any of the words.

Howon earth would I go about doing it?

[mnjofra]

Hi pgds,
try solution from attached file....

Regards,

[ChemistB]

If the column contains more than the key word (i.e. "I am a gunslinger")
Try this arrayed formula (enter with CNTRL SHFT ENTER. {} will appear around formula if done properly)

=SUM(--ISNUMBER(SEARCH($J$1:$J$3,A1)))
where J1:J3 contain your keywords
Does that work for you?

[pgds]

woah, thanks guys

I am gonna give this a try and report back in a bit. Just waiting for my query to finish before I can attempt this

[pgds]

If the column contains more than the key word (i.e. "I am a gunslinger")
Try this arrayed formula (enter with CNTRL SHFT ENTER. {} will appear around formula if done properly)

=SUM(--ISNUMBER(SEARCH($J$1:$J$3,A1)))
where J1:J3 contain your keywords
Does that work for you?

hmm.. not sure its working right, look at the screenshot

formula.png

[mnjofra]

pgds,
what are you doing? My and ChemistB's answer is not connected. I attached file with a final solution in case that you have exact word looking for in A or B column. ChemistB just gave you idea for solution if you have sentence including word looking for. His sample is referenced to range in J column and no any common junction with my sample....
Please carefuly study formulas I put in column C in my sample...
My solution is not need array notification...
Regards,

[pgds]

pgds,
what are you doing? My and ChemistB's answer is not connected. I attached file with a final solution in case that you have exact word looking for in A or B column. ChemistB just gave you idea for solution if you have sentence including word looking for. His sample is referenced to range in J column and no any common junction with my sample....
Please carefuly study formulas I put in column C in my sample...
Regards,

No wonder I am confused, ok maybe if I explain a little more.

I actually have 4 columns, the 1st column I can ignore, then there are 3 with various sentences of text. if any of my keywords appear in any of those sentences then I need a marker (1) if not then (0)

Attached is a sample of my spreadsheet if it helps. How can I sort this?

sample.xlsx

[ChemistB]

I entered a modified Arrayed formula in Column E
In E2
=(SUM(--ISNUMBER(SEARCH($J$2:$J$4,B2:D2)))>0)+0
Is that what you are looking for?

[pgds]

I entered a modified Arrayed formula in Column E
In E2
=(SUM(--ISNUMBER(SEARCH($J$2:$J$4,B2:D2)))>0)+0
Is that what you are looking for?

Rock n Roll!!

thank you very much