Search/find function for an entire column

moon_

New Member
Joined
Jun 10, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have this function that searches column A for words that I listed in column D and returns "found" or "not found".
In the formula below, I only have to search for 4 words in column A so the formula refers to $D$4:$D$8.

Excel Formula:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$4:$D$8,A5)))>0, "found", "not found")

I want to be able to add new words in column D so I referenced the entire column when searching. Somehow the SEARCH function messes up and returns an incorrect found/not found response. Can anyone help me?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Can you provide list of all the words in column D
 
Upvote 0
Hi,
I created this dummy data. Words I want to search for is not in column B. Basically, I want to add words every now and then to column B and I want column C to automatically update and include the new words in the search.

book 2.xlsx
ABC
1Search TermReferenceFOUND?
2gaming chairofficenot found
3office chairstudyfound
4desk chairergonomicnot found
5ergonomic office chairexecutivefound
6ergonomic chairfound
7computer chairnot found
8gamer chairnot found
9desk chair no wheelsnot found
10computer desk chairnot found
11white desk chairnot found
12game chairnot found
13office chair wheelsfound
14big and tall office chairfound
15rolling chairnot found
16pink chairnot found
17white office chairfound
18executive office chairfound
19office desk chairfound
20gaming chair cheapnot found
Sheet1
Cell Formulas
RangeFormula
C2:C20C2=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$5,A2)))>0, "found", "not found")
 
Upvote 0
Hi & welcome to MrExcel.
How about
Excel Formula:
=IF(SUMPRODUCT((B$2:B$10<>"")*(ISNUMBER(SEARCH($B$2:$B$10,A2))))>0, "found", "not found")
 
Upvote 0
searches column A for words
Your current formula actually does not search for "words" but for text strings. That may well be what you want but if words are important to you, you need to do a bit more.
Below I have left your formula in column D and note that row 6 shows found when, in my mind, none of the words were found.
My suggested alternative in column C.

It is possible that in your version of Excel you may need to confirm this formula with Ctrl+Shift+Enter, not just Enter.

21 06 10.xlsm
ABCD
1Search TermReferenceFOUND?
2gaming chairofficenot foundnot found
3office chairstudyfoundfound
4desk chairergonomicnot foundnot found
5ergonomic office chairexecutivefoundfound
6police officernot foundfound
7computer chairnot foundnot found
8gamer chairnot foundnot found
9desk chair no wheelsnot foundnot found
10computer desk chairnot foundnot found
11white desk chairnot foundnot found
12game chairnot foundnot found
13office chair wheelsfoundfound
14big and tall office chairfoundfound
15rolling chairnot foundnot found
16pink chairnot foundnot found
17white office chairfoundfound
18executive office chairfoundfound
19office desk chairfoundfound
20gaming chair cheapnot foundnot found
Check words
Cell Formulas
RangeFormula
C2:C20C2=IF(COUNT(SEARCH(" "&B$2:B$10&" "," "&A2&" ")),"","not ")&"found"
D2:D20D2=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$5,A2)))>0, "found", "not found")
 
Upvote 0
Hi & welcome to MrExcel.
How about
Excel Formula:
=IF(SUMPRODUCT((B$2:B$10<>"")*(ISNUMBER(SEARCH($B$2:$B$10,A2))))>0, "found", "not found")
This worked for me, thank you so much Mr Fluff!
 
Upvote 0
Your current formula actually does not search for "words" but for text strings. That may well be what you want but if words are important to you, you need to do a bit more.
Below I have left your formula in column D and note that row 6 shows found when, in my mind, none of the words were found.
My suggested alternative in column C.

It is possible that in your version of Excel you may need to confirm this formula with Ctrl+Shift+Enter, not just Enter.

21 06 10.xlsm
ABCD
1Search TermReferenceFOUND?
2gaming chairofficenot foundnot found
3office chairstudyfoundfound
4desk chairergonomicnot foundnot found
5ergonomic office chairexecutivefoundfound
6police officernot foundfound
7computer chairnot foundnot found
8gamer chairnot foundnot found
9desk chair no wheelsnot foundnot found
10computer desk chairnot foundnot found
11white desk chairnot foundnot found
12game chairnot foundnot found
13office chair wheelsfoundfound
14big and tall office chairfoundfound
15rolling chairnot foundnot found
16pink chairnot foundnot found
17white office chairfoundfound
18executive office chairfoundfound
19office desk chairfoundfound
20gaming chair cheapnot foundnot found
Check words
Cell Formulas
RangeFormula
C2:C20C2=IF(COUNT(SEARCH(" "&B$2:B$10&" "," "&A2&" ")),"","not ")&"found"
D2:D20D2=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$5,A2)))>0, "found", "not found")
Hey,
Thank you for your help. In my original post, I am referring to text strings instead but forgot the term for it so I used "words" instead. I will definitely be saving this for future use. Thanks Mr Peter!
 
Upvote 0
Hey,
Thank you for your help. In my original post, I am referring to text strings instead but forgot the term for it so I used "words" instead. I will definitely be saving this for future use. Thanks Mr Peter!
No problem. (y)

.. then in that case you could also use this (still may need C+S+E confirmation)

21 06 10.xlsm
ABC
1Search TermReferenceFOUND?
2gaming chairofficenot found
3office chairstudyfound
4desk chairergonomicnot found
5ergonomic office chairexecutivefound
6police officerfound
7computer chairnot found
8gamer chairnot found
9desk chair no wheelsnot found
10computer desk chairnot found
11white desk chairnot found
12game chairnot found
13office chair wheelsfound
14big and tall office chairfound
15rolling chairnot found
16pink chairnot found
17white office chairfound
18executive office chairfound
19office desk chairfound
20gaming chair cheapnot found
Check words
Cell Formulas
RangeFormula
C2:C20C2=IF(COUNT(SEARCH(B$2:B$10,A2)/(B$2:B$10<>"")),"","not ")&"found"
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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