Find item(s) in a cell and match to item(s) in a list

Hello,
I am working on a decision-making tool which will help doctors choose an appropriate drug given a patient’s symptoms and the medications he is currently taking. Cell E9 (labeled “FirstChoice”) will give the doctor treatment instructions which includes a drug category (or categories) from which to choose a drug. Cell F9 (labeled “ProductsFirst”) then will look for the drug category (or categories) in E9 and output the corresponding brand name drug product(s).

Cell E10 (“Alternative”) will give an optional drug category choice and also pull the brand name into F10 (the same concept as “FirstChoice”).

The drug categories and products are:
Category Product
SAMA Atrovent
LAMA Spiriva
SABA Proair
LABA Serevent
ICS Aerobid
SAMA+SABA Combivent
LAMA+LABA Anoro
ICS+LABA Advair

I started thinking that I could do this within Excel using text functions (e.g., FIND, SEARCH, MID, LEN, etc.) and a VLOOKUP array, or INDEX-MATCH but quickly realized that that was becoming overwhelming and cumbersome and that VBA would be more efficient. I now think I need to use VBA functions such as FOR EACH, LOOP, FIND NEXT, etc. but having never written such a complicated loop, find myself at a loss as to where to even start.

I’ve attached a partial copy of the workbook. I deleted non-relevant worksheets so some macros will not work. The sheet of interest is “Recommendation.” I manually entered outputs into cells E9, F9, E10, and F10 as examples.

Many thanks in advance for your expertise. You all are the best!

Some sheets are missing. In order for the macros to work all the sheets need to be in the workbook.

Thanks for your reply, Lewis. I intentionally deleted non-relevant sheets for proprietary reasons. I don't think they are critical to my request but if you believe otherwise, I will re-post the workbook with the sheets but with deleted content on those sheets or else I'll re-post an example workbook without those macros. Thank you for your willingness to help.

Seleseped

It would be best with some sample data and all worksheets and macros that you need in place. This way someone could help you specifically instead of unknowns.

I'll do that but won't be able to do so until Monday when I'm back at work.

I’ve attached the abbreviated workbook with only pertinent sheets included. I hope this will be enough to allow you to help me. Your time and assistance are MUCH appreciated.
(If anybody is looking at the tool for clinical content, the output is TOTALLY bogus and not to be used in any situation.)

Not getting any solutions offered from this help forum, I posted the question on another. The solution may be found at https://www.mrexcel.com/forum/excel-...ched-item.html. Thanks to all of you who took the time to consider my question on this forum.