Combining IF with Search Function

searchingforhelp

New Member
Joined
Nov 11, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am attempting to no avail combine two formulas. My goal is to locate a specific word in a cell that contains other characters/ words by utilizing the SEARCH function. If the word is located in a cell, I would like another cell to indicate "Found". If the word is not located in the cell, it will indicate "Not Found"

Below are two function I am attempting to combined:
=IF(SEARCH(B5,B2),"Found")
=IFERROR(SEARCH(B5,B2),"Not Found").

Below (or attached) is an image I am referencing with the formulas above.

1605130789097.png


Lastly, is there a way in using the functions above to search multiple columns for the same word? I written a separate sentence in F2 for an example.

Greatly appreciate any help I can get and if possible can the formulas be separated per question so I can distinguish between the formulas for learning purposes.

Thanks!!
 

Attachments

  • 1605130425156.png
    1605130425156.png
    10.4 KB · Views: 5
  • 1605130578526.png
    1605130578526.png
    6.6 KB · Views: 5
  • 1605130606571.png
    1605130606571.png
    10.6 KB · Views: 3

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,304
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=if(isnumber(search(" "&B5&" "," "&B2&" ")),"Found", "Not found")
This will distinguish between run & running, as long as there is no punctuation within the phrase.
 

searchingforhelp

New Member
Joined
Nov 11, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=if(isnumber(search(" "&B5&" "," "&B2&" ")),"Found", "Not found")
This will distinguish between run & running, as long as there is no punctuation within the phrase.
Thank you Fluff, truly appreciate it. I noticed if the word "run" is proceeded by "run)". The formula result was "Not Found". Is there a way to override these type of instances? I noticed when using only =IFERROR(SEARCH(B5,B2),"Not Found"), it located the word "run" within "run)" however as a result it gave a number number.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,304
Office Version
  1. 365
Platform
  1. Windows
You can use
Excel Formula:
=if(isnumber(search(B5,B2)),"Found", "Not found")
But that will return true for words words like runt, brunt etc. Not sure if that's a problem.
 

searchingforhelp

New Member
Joined
Nov 11, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can use
Excel Formula:
=if(isnumber(search(B5,B2)),"Found", "Not found")
But that will return true for words words like runt, brunt etc. Not sure if that's a problem.
Perfect! This worked! Before I check off, can you provide insight regarding my second question in the first post. How can I use this formula to search multiple columns? For example, search in column B2 and F2 for the word run. Thanks again for taking your time to address my questions.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,304
Office Version
  1. 365
Platform
  1. Windows
Maybe
Excel Formula:
=if(or(isnumber(search(B5,B2)),isnumber(search(B5,B2))),"Found", "Not found")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,304
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,119
Messages
5,622,837
Members
415,934
Latest member
adstocking

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
Top