Search/find function for an entire column

[moon_]

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.

=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?

 

[Sufiyan97]

Can you provide list of all the words in column D

 

[moon_]

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
	A	B	C
1	Search Term	Reference	FOUND?
2	gaming chair	office	not found
3	office chair	study	found
4	desk chair	ergonomic	not found
5	ergonomic office chair	executive	found
6	ergonomic chair		found
7	computer chair		not found
8	gamer chair		not found
9	desk chair no wheels		not found
10	computer desk chair		not found
11	white desk chair		not found
12	game chair		not found
13	office chair wheels		found
14	big and tall office chair		found
15	rolling chair		not found
16	pink chair		not found
17	white office chair		found
18	executive office chair		found
19	office desk chair		found
20	gaming chair cheap		not found
Sheet1

Cell Formulas
Range	Formula
C2:C20	C2	=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$5,A2)))>0, "found", "not found")

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(SUMPRODUCT((B$2:B$10<>"")*(ISNUMBER(SEARCH($B$2:$B$10,A2))))>0, "found", "not found")

 

[Peter_SSs]

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
	A	B	C	D
1	Search Term	Reference	FOUND?
2	gaming chair	office	not found	not found
3	office chair	study	found	found
4	desk chair	ergonomic	not found	not found
5	ergonomic office chair	executive	found	found
6	police officer		not found	found
7	computer chair		not found	not found
8	gamer chair		not found	not found
9	desk chair no wheels		not found	not found
10	computer desk chair		not found	not found
11	white desk chair		not found	not found
12	game chair		not found	not found
13	office chair wheels		found	found
14	big and tall office chair		found	found
15	rolling chair		not found	not found
16	pink chair		not found	not found
17	white office chair		found	found
18	executive office chair		found	found
19	office desk chair		found	found
20	gaming chair cheap		not found	not found
Check words

Cell Formulas
Range	Formula
C2:C20	C2	=IF(COUNT(SEARCH(" "&B$2:B$10&" "," "&A2&" ")),"","not ")&"found"
D2:D20	D2	=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$5,A2)))>0, "found", "not found")

 

[moon_]

Hi & welcome to MrExcel.
How about

=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!

 

[moon_]

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
	A	B	C	D
1	Search Term	Reference	FOUND?
2	gaming chair	office	not found	not found
3	office chair	study	found	found
4	desk chair	ergonomic	not found	not found
5	ergonomic office chair	executive	found	found
6	police officer		not found	found
7	computer chair		not found	not found
8	gamer chair		not found	not found
9	desk chair no wheels		not found	not found
10	computer desk chair		not found	not found
11	white desk chair		not found	not found
12	game chair		not found	not found
13	office chair wheels		found	found
14	big and tall office chair		found	found
15	rolling chair		not found	not found
16	pink chair		not found	not found
17	white office chair		found	found
18	executive office chair		found	found
19	office desk chair		found	found
20	gaming chair cheap		not found	not found
Check words

Cell Formulas
Range	Formula
C2:C20	C2	=IF(COUNT(SEARCH(" "&B$2:B$10&" "," "&A2&" ")),"","not ")&"found"
D2:D20	D2	=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!

 

[Peter_SSs]

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.

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

21 06 10.xlsm
	A	B	C
1	Search Term	Reference	FOUND?
2	gaming chair	office	not found
3	office chair	study	found
4	desk chair	ergonomic	not found
5	ergonomic office chair	executive	found
6	police officer		found
7	computer chair		not found
8	gamer chair		not found
9	desk chair no wheels		not found
10	computer desk chair		not found
11	white desk chair		not found
12	game chair		not found
13	office chair wheels		found
14	big and tall office chair		found
15	rolling chair		not found
16	pink chair		not found
17	white office chair		found
18	executive office chair		found
19	office desk chair		found
20	gaming chair cheap		not found
Check words

Cell Formulas
Range	Formula
C2:C20	C2	=IF(COUNT(SEARCH(B$2:B$10,A2)/(B$2:B$10<>"")),"","not ")&"found"