Keywords match in excel

Daiwik

New Member
Joined
Jan 12, 2016
Messages
15
Hi,

I have a list of keywords in Sheet A (2000 rows) and I have a sheet B (column B), where I want to highlight Column B or get YES/NO (in nearby column D) if the cell of column B contains any keyword.

For example - Column B cell contains "
30secondMBA.com" and I have a keyword "MBA".
If I use ISNUMBER function, it will be insanely huge which doesn't seem worthy. Please please help me, I have to prepare this sheet today :(
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try using a Conditional Formatting formula along the lines of this:

=SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2))))

where A2:A5 are your keywords and your strings are in column C (starting in row 2).

Or if you want a formula to return YES/NO, you can use the formula (regular formula in the worksheet, not CF formula):

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2)))),"YES","NO)
 
Upvote 0
Note that this will return potentially incorrect results if you have any blank cells in the A range. Perhaps subtract the count of blanks in the range.
 
Upvote 0
Try using a Conditional Formatting formula along the lines of this:

=SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2))))

where A2:A5 are your keywords and your strings are in column C (starting in row 2).

Or if you want a formula to return YES/NO, you can use the formula (regular formula in the worksheet, not CF formula):

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2)))),"YES","NO)

Thanks very much 63falcondude, :)
Formula is working fine in returning YES/NO but cond.Format. is not working properly. Actually it's highlighting every cell which is not blank. My priority is to highlight keyword matching cells. Please help, my boss will be very happy :-D
 
Upvote 0
Thanks very much 63falcondude, :)
Formula is working fine in returning YES/NO but cond.Format. is not working properly. Actually it's highlighting every cell which is not blank. My priority is to highlight keyword matching cells. Please help, my boss will be very happy :-D

If the regular formula worked fine then the CF formula will work fine as well as long as it is applied correctly. See example below.

Highlight C2:C4 > Conditional Formatting > New Rule > Use a Formula > =SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2))))
Format however you like > OK

The result will look as follows


Excel 2010
ABC
1KeywordsString
2apple30secondMBA.com
3mouseThe quick brown fox
4catCaterpillars are fun
5MBA
Sheet1


Make sure that there are not any blank cells in the column A range (A2:A5 in this example).
 
Upvote 0
If the regular formula worked fine then the CF formula will work fine as well as long as it is applied correctly. See example below.

Highlight C2:C4 > Conditional Formatting > New Rule > Use a Formula > =SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,C2))))
Format however you like > OK

The result will look as follows

Excel 2010
ABC
KeywordsString
apple
mouseThe quick brown fox
cat
MBA

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]30secondMBA.com[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Caterpillars are fun[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1



Make sure that there are not any blank cells in the column A range (A2:A5 in this example).

Thanks 63falcondude, You made my day. It's working great now. Now i'll work on reducing blank cells in the Keyword list because i just got informed that there could be some addition or removing of data. Thanks again for your cooperation :)
 
Upvote 0
Thanks 63falcondude, You made my day. It's working great now. Now i'll work on reducing blank cells in the Keyword list because i just got informed that there could be some addition or removing of data. Thanks again for your cooperation :)

If that's the case, I would make the data from column A into a table and then use the table's column name in place of $A$2:$A$5.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top