Searching for unknown text string in a range and then matching it to a reference table

Hi everyone,

First time poster. I am hoping you folks can kindly help me with a very complicated set of questions. I have been trying to look this up for the last two days and I cannot seem to figure out a way to do it.

This post has been revised due to breadth of the question.

Essentially - I want to be able to have a function that searches the third row in column A for a text string because only the suspected diagnosis will be filled in, and the other cells will be blank. The problem is that I do not know what string I am searching for! It could be A, B, C, or A and B, etc. So how do I get excel to search a range of cells for to see if ISTEXT=true and then return to me what the value of the text was so I know what I am dealing with.

Please see below for a revised question. Find my excel sample worksheet attached Colors.xlsx. Thank you!