Query in Google Sheets

Not exactly sure I understand. Maybe try =COUNTIFS(list of names,name) will return 0 if the name is not in the list of names or something greater than 0 if the name is there.
Or =IFERROR(MATCH(name,list of names,0),"name not present") will return a number (corresponding to the position of name in the range vector) if the name exists or "name not present" if it does not.
Or some other variation based on a count if function or lookup function.

Mr. Shorty's COUNTIFS suggestion should still work, or at least it should in Excel.
The caveat being the the list of names argument would need to contain the full file path for the file containing list X since presumably the workbook containing that list would be closed.
So say that list X is in cells A2:A4 on Sheet1 of Book1000 on the D drive in the Documents directory and contains Name1, Name2 and Name3
List Y is in cells A2:A3 and contains Name1 and Name4 and that cell B2 contains the formula: =COUNTIFS('D:\Documents\[Book1000.xlsx]Sheet1'!A$2:A$4,A2)
The formula is dragged down to cell B3
Cell B2 will display 1 because Name1 also exists in list X and cell B3 will display zero because Name4 doesn't exist is list X.
Note that if COUNTIFS doesn't produce expected results you might also try: =SUMPRODUCT(--('D:\Documents\[Book1000.xlsx]Sheet1'!A$2:A$4=A2))
I don't know much about google sheets, so this is probably the best I can do.