'kiss' solution - 'keep it simple and stupid' (aka 'quick 'n dirty'):
assuming: 'all in one row'!, names to search 'for' in Sheet1.1:1 (row1), names to search 'in' in Sheet2.1:1 (row1), otherwise adapt to your ranges / names,
put =IFERROR("Sheet2."&ADDRESS(1;MATCH(A1;$Sheet2.1:$Sheet2.1;0);4);"not found")
in Sheet1.A2, copy as wide as needed,
ok?
P.S. what do you want to do once you found the names? just know if / where it is? do some edit? delete? if it's useful to have quick access to the cell may be someone can change the formula to include a hyperlink which on click will jump to the search result?
P.S. II tried it myself, you may try: =HYPERLINK("#"&IFERROR("Sheet2."&ADDRESS(1;MATCH(A1;$Sheet2.1:$Sheet2.1;0);4);"not found");IFERROR("Sheet2."&ADDRESS(1;MATCH(A1;$Sheet2.1:$Sheet2.1;0);4);"not found") & " - ctrl-click to enter")
worked here, but 'waw'! - without any warranty'
P.S. III 'solved marks' and 'likes' welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the "^" above it if you 'like' the answer,
"v" if you don't,
do not! use 'answer' to add info to your question, either edit the question or add a comment,
use 'answer' only if you found a solution yourself ...
[edit]
P.S. IV just had the idea you could have meant 'the names are each in a single row' instead of all ... in that case place the formula besides the name column and try to adapt it to search columns instead of rows ...
[/edit]
How are the names formated ... Are they all in the same cell, or in a row/colum? An example would be helpful.
im sorry if im not clear enough, added an edit
I think i could throw together a maco function, which as input gets a cell range, and looks for cell ranges with identical data and then outputs their location. Would that work for you?