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

 

[jgruberman]

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.

Fair enough, thank you for the help. This got me a long ways!

 

[Cubist]

Fair enough, thank you for the help. This got me a long ways!

You're welcome. Glad we can assist. One caution about dependent lists is that you have to select from left to right. If you change something in the middle, you'd have to reselect anything to the right of the one you changed. For example, you've picked symptoms 1-5, but then you wanted to change the third symptom. Now the 4th and 5th selected symptoms are not dependent on the new 3rd symptom. You'd have some how make people reselect 4th and 5th again. That'd require some VBA. Just something to consider.

 

[jgruberman]

You're welcome. Glad we can assist. One caution about dependent lists is that you have to select from left to right. If you change something in the middle, you'd have to reselect anything to the right of the one you changed. For example, you've picked symptoms 1-5, but then you wanted to change the third symptom. Now the 4th and 5th selected symptoms are not dependent on the new 3rd symptom. You'd have some how make people reselect 4th and 5th again. That'd require some VBA. Just something to consider.

I did find a link from your description that does include some VBA to reset the dependent options(Creating a Dependent Drop Down List in Excel [Step-by-Step Tutorial]). I'm going to spend some time sorting through the values and try to order them better. I think my next step is to try to get all of the "Hot Feet" in a single symptom column, the same for all the others. It's a process, but I do enjoy figuring out things like this. Thank you again