Solution 1:

You could use the following function

=IFERROR(IF(MATCH(A1,Sheet1!$A:$A,0),"yes",),"no")

Starting from the inside out

Match, looks in sheet1 column A to see if there is a value which matches cell A1 of the current sheet (sheet2). If there is an exact match it returns the row number.

The if statement. If match returns something (number 1 or greater) this is taken as true and returns "yes"

iferror. If match doesn't find anything it returns a na error. Iferror makes this return the last "no"

Solution 2:

VLOOKUP should work...

=IF(ISNA(VLOOKUP(A1,Sheet1!$A:$A,1,false)),"NO","YES")

If no match is found, VLOOKUP return NA. So we see if its result ISNA? Then return NO otherwise YES