Search multiple rows for a single result?

[jgruberman]

I am trying to compile some data for a game that has a vet simulator built into it. My goal is to select from a dropdown(like a data filter) a number of sick pet symptoms and return a single illness name. Here is a sample of data. In this example, I would select "Icy Fur", "Low Temperature", "Overly Moist Skin", and "Watery Eyes" separately and it would return "Frosty Fur Flu". However, if I were to select "Icy Fur" and "Low Temperature" only, then it would show "Frosty Fur Flu" and "Frozen Mange" since enough Minor Symptoms were not selected to determine the correct Sickness.

Honestly, I'm not even sure if this is possible with Excel but it seems like it could be... just with possibly an extensive amount of filtering?

Sickness	Major Symptom	Minor Symptoms	Exam Table Cure
Derpy Doggy	Extreme Lethargy	Inflamed Cuteness	Antifungal Spray
Derpy Doggy	Extreme Lethargy	Sluggish Heartbeat	Antifungal Spray
Faking Healthy	none	Excessive Earwax	Fixitol Treat
Faking Healthy	none	High temperature	Fixitol Treat
Fleas	Fleas	None	Parasite Killer Spray
Frosty Fur Flu	Icy Fur	Low Temperature	Essence of Placebo
Frosty Fur Flu	Icy Fur	Overly Moist Skin	Essence of Placebo
Frosty Fur Flu	Icy Fur	Watery Eyes	Essence of Placebo
Frozen Mange	Icy Fur	Excessively Wet Nose	Organic Disinfectant Spray
Frozen Mange	Icy Fur	Low Temperature	Organic Disinfectant Spray
Frozen Mange	Icy Fur	Stinky Fur	Organic Disinfectant Spray

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Sickness	Major Symptom	Minor Symptoms	Exam Table Cure		Major	Minor1	Minor2	Minor3	Potential Sickness
2	Derpy Doggy	Extreme Lethargy	Inflamed Cuteness	Antifungal Spray		Icy Fur				Frosty Fur Flu
3	Derpy Doggy	Extreme Lethargy	Sluggish Heartbeat	Antifungal Spray						Frozen Mange
4	Faking Healthy	none	Excessive Earwax	Fixitol Treat
5	Faking Healthy	none	High temperature	Fixitol Treat
6	Fleas	Fleas	None	Parasite Killer Spray
7	Frosty Fur Flu	Icy Fur	Low Temperature	Essence of Placebo
8	Frosty Fur Flu	Icy Fur	Overly Moist Skin	Essence of Placebo
9	Frosty Fur Flu	Icy Fur	Watery Eyes	Essence of Placebo
10	Frozen Mange	Icy Fur	Excessively Wet Nose	Organic Disinfectant Spray
11	Frozen Mange	Icy Fur	Low Temperature	Organic Disinfectant Spray
12	Frozen Mange	Icy Fur	Stinky Fur	Organic Disinfectant Spray
Sheet10

Cell Formulas
Range	Formula
J2:J3	J2	=LET(g,GROUPBY(A2:B12,C2:C12,ARRAYTOTEXT,,0),FILTER(INDEX(g,,1),(INDEX(g,,2)=F2)*ISNUMBER(SEARCH(G2,INDEX(g,,3))*SEARCH(H2,INDEX(g,,3))*SEARCH(I2,INDEX(g,,3)))))
Dynamic array formulas.

 

[Jeffrey Mahoney]

How about something like this?

Book1
	A	B	C	D	E	F	G	H	I
1	Sickness	Major Symptom	Minor Symptom	Exam Table Cure		Minor Short List		Major Symptom	Minor Symptom
2	Derpy Doggy	Extreme Lethargy	Inflamed Cuteness	Antifungal Spray		Low Temperature		Icy Fur	Stinky Fur
3	Derpy Doggy	Extreme Lethargy	Sluggish Heartbeat	Antifungal Spray		Overly Moist Skin
4	Faking Healthy	None	Excessive Earwax	Fixitol Treat		Watery Eyes		Sickness	Exam Table Cure
5	Faking Healthy	None	High Temperature	Fixitol Treat		Excessively Wet Nose		Frozen Mange	Organic Disinfectant Spray
6	Fleas	Fleas	None	Parasite Killer Spray		Low Temperature
7	Frosty Fur Flu	Icy Fur	Low Temperature	Essence of Pacebo		Stinky Fur
8	Frosty Fur Flu	Icy Fur	Overly Moist Skin	Essence of Pacebo
9	Frosty Fur Flu	Icy Fur	Watery Eyes	Essence of Pacebo
10	Frozen Mange	Icy Fur	Excessively Wet Nose	Organic Disinfectant Spray
11	Frozen Mange	Icy Fur	Low Temperature	Organic Disinfectant Spray
12	Frozen Mange	Icy Fur	Stinky Fur	Organic Disinfectant Spray
Sheet2

Cell Formulas
Range	Formula
F2:F7	F2	=FILTER($C$2:$C$12,$B$2:$B$12=H2,"")
H5	H5	=FILTER($A$2:$A$12,IF(I2<>"",($B$2:$B$12=H2)*($C$2:$C$12=I2),($B$2:$B$12=H2)),"")
I5	I5	=FILTER($D$2:$D$12,IF(I2<>"",($B$2:$B$12=H2)*($C$2:$C$12=I2),($B$2:$B$12=H2)),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=$B$2:$B$12
I2	List	=$F$2#

 

[Cubist]

I would select "Icy Fur", "Low Temperature", "Overly Moist Skin", and "Watery Eyes" separately and it would return "Frosty Fur Flu"

@Jeffrey Mahoney
Based on the OP, they should be able to select up to 3 minor symptoms.

 

[jgruberman]

I'm going to take some time and look at both of these and let you know. I did reformat the data to what I think would be a bit easier to work with, so I'm going to figure how to translate those formulas to the new data. I'm not sure if this works easier or not.

Sickness	Symptom 1	Symptom 2	Symptom 3	Symptom 4	Symptom 5	Exam Table Cure	Surgical Cure
Derpy Doggy	Extreme Lethargy	Inflamed Cuteness	Sluggish Heartbeat			Antifungal Spray	Refill Nose
Faking Healthy	none	Excessive Earwax	High Temperature			Fixitol Treat	Extract Excess Cuteness
Fleas	Fleas					Parasite Killer Spray	Unblock Chute
Frosty Fur Flu	Icy Fur	Low Temperature	Overly Moist Skin	Watery Eyes		Essence of Placebo	Unblock Kibble Chute Blockage
Frozen Mange	Icy Fur	Excessively Wet Nose	Low Temperature	Stinky Fur		Organic Disinfectant Spray	Refill Nose
Gilded Guts Disorder	Golden Poop	Overly Moist Skin	Rapid Heartbeat	Watery Eyes		Essence of Placebo	Tum Tum Readjustment

 

[Cubist]

With the new layout, try

Book1
	A	B	C	D	E	F	G	H
1	Sickness	Symptom 1	Symptom 2	Symptom 3	Symptom 4	Symptom 5	Exam Table Cure	Surgical Cure
2	Derpy Doggy	Extreme Lethargy	Inflamed Cuteness	Sluggish Heartbeat			Antifungal Spray	Refill Nose
3	Faking Healthy	none	Excessive Earwax	High Temperature			Fixitol Treat	Extract Excess Cuteness
4	Fleas	Fleas					Parasite Killer Spray	Unblock Chute
5	Frosty Fur Flu	Icy Fur	Low Temperature	Overly Moist Skin	Watery Eyes		Essence of Placebo	Unblock Kibble Chute Blockage
6	Frozen Mange	Icy Fur	Excessively Wet Nose	Low Temperature	Stinky Fur		Organic Disinfectant Spray	Refill Nose
7	Gilded Guts Disorder	Golden Poop	Overly Moist Skin	Rapid Heartbeat	Watery Eyes		Essence of Placebo	Tum Tum Readjustment
8
9	Symptom 1	Symptom 2	Symptom 3	Symptom 4	Symptom 5	Sickness
10	watery eyes	overly moist skin	golden poop			Gilded Guts Disorder
Sheet12

Cell Formulas
Range	Formula
F10	F10	=FILTER(A2:A7,ISNUMBER(BYROW(DROP(REDUCE("",A10:E10,LAMBDA(a,b,HSTACK(a,SEARCH(b,BYROW(B2:F7,ARRAYTOTEXT))))),,1),PRODUCT)),"")

 

[Jeffrey Mahoney]

@Jeffrey Mahoney
Based on the OP, they should be able to select up to 3 minor symptoms.

Then @Cubist I think you did a good job

 

[jgruberman]

With the new layout, try

Book1
	A	B	C	D	E	F	G	H
1	Sickness	Symptom 1	Symptom 2	Symptom 3	Symptom 4	Symptom 5	Exam Table Cure	Surgical Cure
2	Derpy Doggy	Extreme Lethargy	Inflamed Cuteness	Sluggish Heartbeat			Antifungal Spray	Refill Nose
3	Faking Healthy	none	Excessive Earwax	High Temperature			Fixitol Treat	Extract Excess Cuteness
4	Fleas	Fleas					Parasite Killer Spray	Unblock Chute
5	Frosty Fur Flu	Icy Fur	Low Temperature	Overly Moist Skin	Watery Eyes		Essence of Placebo	Unblock Kibble Chute Blockage
6	Frozen Mange	Icy Fur	Excessively Wet Nose	Low Temperature	Stinky Fur		Organic Disinfectant Spray	Refill Nose
7	Gilded Guts Disorder	Golden Poop	Overly Moist Skin	Rapid Heartbeat	Watery Eyes		Essence of Placebo	Tum Tum Readjustment
8
9	Symptom 1	Symptom 2	Symptom 3	Symptom 4	Symptom 5	Sickness
10	watery eyes	overly moist skin	golden poop			Gilded Guts Disorder
Sheet12

Cell Formulas
Range	Formula
F10	F10	=FILTER(A2:A7,ISNUMBER(BYROW(DROP(REDUCE("",A10:E10,LAMBDA(a,b,HSTACK(a,SEARCH(b,BYROW(B2:F7,ARRAYTOTEXT))))),,1),PRODUCT)),"")

This is definitely the direction I'm going for... is there some sort of way to choose an option from Column B and it only show me the applicable options in Column C and later, or is it at that point just using filtering options from the top? I guess the way that I originally envisioned it is a pick list similar to the below screenshot and based on selections, it spit out the return options:

 

[jgruberman]

I was able to follow this link to be able to get multiple selections in a single dropdown, but maybe it's possible to parse the values into a search across the array of symptoms?

How to create multi-select drop down list in Excel

Discover how to create an Excel drop down to select multiple items. Depending on your needs, it can be with or without duplicates and allow for item removal.

www.ablebits.com

 

[Cubist]

There are many resources on "dependent drop down lists". That's a different question than the one you asked in this thread. Please start a new thread for a new question. Once you have the drop down list figured out, replace the references with the drop down selection in the formula provided above.