Search Text In A Cell + If Function

katsuya

New Member
Joined
Jun 25, 2011
Messages
4
Kindly provide help on this one. Basically, formula that I want is this:
If cell A1= "Campaign" AND cell B2 CONTAINS text "Campaign", if true return "Campaign", if not return blank. Thanks.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

Use below formula in C1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 68px;"> <col style="width: 68px;"> <col style="width: 214px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>Campaign</td> <td>Campaign</td> <td>Campaign</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>Campaign</td> <td>r</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Campaign</td> <td>Campaign</td> <td>Campaign</td></tr></tbody></table>
<table style="font-size: 10pt; border-color: rgb(0, 255, 0); color: rgb(0, 0, 0); border-style: groove; font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C1</td> <td>=IF(AND(A1="Campaign",B1="Campaign"),"Campaign","")</td></tr> <tr> <td>C2</td> <td>=IF(AND(A2="Campaign",B2="Campaign"),"Campaign","")</td></tr> <tr> <td>C3</td> <td>=IF(AND(A3="Campaign",B3="Campaign"),"Campaign","")</td></tr></tbody></table></td></tr></tbody></table>
 
Upvote 0
Kindly provide help on this one. Basically, formula that I want is this:
If cell A1= "Campaign" AND cell B2 CONTAINS text "Campaign", if true return "Campaign", if not return blank. Thanks.

Perhaps this:
Code:
=IF(AND(A1="campaign",COUNTIF(B2,"*campaign*")),"campaign","")
Is that something you can work with?
 
Upvote 0
Man thanks. I've been juggling with ISERROR(SEARCH... but not getting anywhere. You nailed it right on the money. Care to elaborate how come countif returns a text? Never knew it can do it. Many, many thanks.
And thank you also excelsishya for the reply but Ron's fit it well.

Perhaps this:
Code:
=IF(AND(A1="campaign",COUNTIF(B2,"*campaign*")),"campaign","")
Is that something you can work with?
 
Upvote 0
The way I used COUNTIF in this part of the formula:
COUNTIF(B2,"*campaign*")
It counts the number of cells that *contain* campaign.

Since we're only testing one cell, that section returns either 1 or zero.

Excel interprets a 1 as TRUE and a zero as FALSE.

For simple tests, I prefer COUNTIF over ISERROR(SEARCH
because COUNTIF does not throw an error if there's no match.
Consequently, we don't need to trap the error that would be thrown by SEARCH.

I hope that helps.
 
Upvote 0
Yeah thanks for the explanation. I'm still absorbing it. It's quite uncommon, well, for me. Will post again if this works (the actual formula is a little more complex than this) but looks like it will. Thanks again Ron.
 
Upvote 0
Hi there again. I'm integrating the formula but I'm bumping to this wall thing.

Please forgive my nonsense analogy but below is somewhat my database.
Ok, here we go. Column A used to be summarized into just 3 classifications. Yellow, Black, Others(see Column B). But it's now changed to 9 classes (see column C). Due to 9 conditions, I'm breaking it into 2 columns.

Column E (1st batch ok)
=IF(A2="Yellow","Yellow",IF(A2="Yellow + Green","Yellow + Green",IF(A2="Green White","Green White",IF(A2="Green Non-White","Green Non-White",""))))

2nd batch is the problem (this is my approach)
word 'black' and column D is related
This is it
Column F
IF A2 CONTAINS "Black",AND D2 CONTAINS "BAG", if true return "Black - Bag",if(A2 CONTAINS "Black" AND D2 CONTAINS "POUCH", if true return "Black - Pouch",if(A2 CONTAINS "BLACK" AND D2 DOES NOT CONTAIN "POUCH" OR "BAG", if true return "Black", if(AND(A2<> "Yellow",A2<> "Yellow + Green", A2<> "Green White",A2<> "Green Non-White", A2 DOES NOT CONTAIN "BLACK", A2 DOES NOT CONTAIN "Triband", return "Others", If false return "".

Column G
=IF(E2="",F2,E2)

By the way, I've posted this same query in excelforum.com.

http://postimage.org/image/30jv0r1ok
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,608
Messages
6,179,872
Members
452,949
Latest member
Dupuhini

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