Search for multiple values within a single cell

[nickhills1]

Hi All..

I need your wisdom please.
I need to be able to search for multiple values within a single cell, and return a related cell value if true.

In my example, I need to be able to select the search terms (Cow and Horse- column G&H) and then run a command that searches for Vets who treat Cow and Horse from the list, and then return the Vet name who does this. >> Results should be Vet1, Vet2

And then be able to select different animals (maybe Goat and Goldfish) and then run the search command to return Vet3

Thanks in advance for your assistance

 

[shift-del]

Hello nickhills1

Mappe2
	A	B	C	D	E	F	G	H
1	Vet Name	Animals treated	Filter
2	Vet 1	Dog, Horse, Cow, Chicken	WAHR			Chicken		Vet 1
3	Vet 2	Cow, Horse	WAHR			Cow	Y	Vet 2
4	Vet 3	Chicken, Goldfish, Goat	FALSCH			Dog
5	Vet 4	Goldfish, Horse	FALSCH			Goat
6	Vet 5	Chicken	FALSCH			Goldfish
7	Vet 6	Chicken, Cow	FALSCH			Horse	Y
8	Vet 7	Horse, Dog	FALSCH
9	Vet 8	Dog, Goat, Chicken	FALSCH
Tabelle1

Cell Formulas
Range	Formula
H2:H3	H2	=FILTER(A2:A9,C2:C9)
C2:C9	C2	=SUM(ISNUMBER(SEARCH($F$2:$F$7,B2))*($G$2:$G$7="Y"))=COUNTA($G$2:$G$7)
Dynamic array formulas.

 

[nickhills1]

Hello nickhills1

Mappe2
	A	B	C	D	E	F	G	H
1	Vet Name	Animals treated	Filter
2	Vet 1	Dog, Horse, Cow, Chicken	WAHR			Chicken		Vet 1
3	Vet 2	Cow, Horse	WAHR			Cow	Y	Vet 2
4	Vet 3	Chicken, Goldfish, Goat	FALSCH			Dog
5	Vet 4	Goldfish, Horse	FALSCH			Goat
6	Vet 5	Chicken	FALSCH			Goldfish
7	Vet 6	Chicken, Cow	FALSCH			Horse	Y
8	Vet 7	Horse, Dog	FALSCH
9	Vet 8	Dog, Goat, Chicken	FALSCH
Tabelle1

Cell Formulas
Range	Formula
H2:H3	H2	=FILTER(A2:A9,C2:C9)
C2:C9	C2	=SUM(ISNUMBER(SEARCH($F$2:$F$7,B2))*($G$2:$G$7="Y"))=COUNTA($G$2:$G$7)
Dynamic array formulas.

Sir, that is amazing!!! I have been looking for a solution for a couple of days (on and off). I owe you a virtual beer!!!

Sincerely, many thanks

 

[shift-del]

You're welcome.

 

[Peter_SSs]

I would sound a word of caution on that formula. In this example that structure correctly returns Vet 7, but also returns Vet 5 who only treats fish, not dogs and cats.

Nickhills1.xlsm
	A	B	C	D	E	F	G	H
1	Vet Name	Animals treated	Filter
2	Vet 1	Dog, Horsefly, Cow, Chicken	FALSE			Chicken		Vet 5
3	Vet 2	Cow, Horse	FALSE			Cow		Vet 7
4	Vet 3	Chicken, Goldfish, Goat	FALSE			Dog	Y
5	Vet 4	Goldfish, Horse	FALSE			Goat
6	Vet 5	Catfish, Spiny Dogfish	TRUE			Goldfish
7	Vet 6	Chicken, Cow	FALSE			Cat	Y
8	Vet 7	Horse, Dog, Cat	TRUE
9	Vet 8	Dog, Goat, Chicken	FALSE
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=FILTER(A2:A9,C2:C9)
C2:C9	C2	=SUM(ISNUMBER(SEARCH($F$2:$F$7,B2))*($G$2:$G$7="Y"))=COUNTA($G$2:$G$7)
Dynamic array formulas.

Instead you might try this modification

Nickhills1.xlsm
	A	B	C	D	E	F	G	H
1	Vet Name	Animals treated	Filter
2	Vet 1	Dog, Horsefly, Cow, Chicken	FALSE			Chicken		Vet 7
3	Vet 2	Cow, Horse	FALSE			Cow
4	Vet 3	Chicken, Goldfish, Goat	FALSE			Dog	Y
5	Vet 4	Goldfish, Horse	FALSE			Goat
6	Vet 5	Catfish, Cod, Spiny Dogfish	FALSE			Goldfish
7	Vet 6	Chicken, Cow	FALSE			Cat	Y
8	Vet 7	Horse, Dog, Cat	TRUE
9	Vet 8	Dog, Goat, Chicken	FALSE
Sheet2

Cell Formulas
Range	Formula
H2	H2	=FILTER(A2:A9,C2:C9)
C2:C9	C2	=COUNT(IF(G$2:G$7="Y",SEARCH(" "&F$2:F$7&","," "&B2&","),""))=COUNTA(G$2:G$7)

 

[nickhills1]

Thanks both..

I see what you mean Peter, as the search terms are subsets of longer words..

I have applied the formula's to my sheet in question, and at the moment, when there are no search terms selected (no Y's in column G), it is returning all Vets..
Is there a way to force it to return no results (blank) if there are no Y's in column G?

TY

 

[Peter_SSs]

Is there a way to force it to return no results (blank) if there are no Y's in column G?

Sure ..

Nickhills1.xlsm
	A	B	C	D	E	F	G	H
1	Vet Name	Animals treated	Filter
2	Vet 1	Dog, Horsefly, Cow, Chicken	TRUE			Chicken
3	Vet 2	Cow, Horse	TRUE			Cow
4	Vet 3	Chicken, Goldfish, Goat	TRUE			Dog
5	Vet 4	Goldfish, Horse	TRUE			Goat
6	Vet 5	Catfish, Cod, Spiny Dogfish	TRUE			Goldfish
7	Vet 6	Chicken, Cow	TRUE			Cat
8	Vet 7	Horse, Dog, Cat	TRUE
9	Vet 8	Dog, Goat, Chicken	TRUE
Sheet2

Cell Formulas
Range	Formula
H2	H2	=IF(COUNTA(G2:G7),FILTER(A2:A9,C2:C9),"")
C2:C9	C2	=COUNT(IF(G$2:G$7="Y",SEARCH(" "&F$2:F$7&","," "&B2&","),""))=COUNTA(G$2:G$7)