Sorry about the confusion, a nursing instructor here at school needs the
answer and I am trying to help. He has 2 sheets he is trying to get the data
to work with.
On sheet 2 he needs the name of the person in column A that is assigned the
letters "AC" in column C to appear on sheet 1 in column B6. We will then
need to copy the formula to column D, E, F, etc.

Thank you for your time and patience, you have been most helpful.