Numbers: checking if cell with specific text exists
This formula works where A3 is the cell which contains the search string and "Example Semester::A3:A4" is the range to search.

IF(IFERROR(MATCH(A3,Example Semester::A3:A4,0),FALSE),"YES","NO")