Identify a specific symptom based on larger text

Dear Expert and learner,

I have a problem to identify a specific text based on a cell that contain much text.

Can you help me to solve my problem.
I already attach the example and the result that I want.

Thanks a Lot,

Originally Posted by Glenn Kennedy

1. Your merged cells are a COMPLETE disaster. Get RID of them. The man who invented them should be shot. Each cage MUST be ONE row. Make the rows taller... do not merge them!!!

2. Amended formula in column AE... cell whatever.

=IF(ISNUMBER(SEARCH("Insang pucat",AA4)*SEARCH("white nodule",AA4)),"Francisella sp","")&" "&IF(ISNUMBER(SEARCH("darkening body",Z4)*SEARCH("erosi",Z4)*SEARCH("abses",Z4)),"Streptococcus sp","")&" "&IF(ISNUMBER(SEARCH("hemorhaging",Z4)),"Aeromonas sp","")

Paste into each cell manually adjusting the row references EVERY TIME. This is needed because of those USELESS merged cells. Without them, you could just drag the formula down.

3. Refer to the red highlights in cage ND06 and explain WHY only Francisella spp. is the expected answer.

4. Refer to this www:

https://excel.solutions/2016/10/usin...ctively-excel/

Yeah that my friend workbook, I already tell em to standarized any table that they working on so the formula can easily read the table.
thanks a lot Glenn, thats work well.

Note. case in ND06 is miss expected result, thats why I want to search formula that can fix this problem.