How to find a list of names in another sheets

i have a list of names and i want to find those names in another sheet. how do i do this without manually inputing each name by ctrl+f?

the names are all in a single row and so is the other sheet

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?

Hello (again)

here the (promised) macro function. Just put it into Tools → Macros → “Edit Macros” and you can call it from inside any cell via

  • The 1st argument is the range to be searched for quoted as a string. The range can span multiple rows and columns.

One limitation i have to meantion. The area that is searched on each sheet is “only” A1:Z100
If that is not sufficient you can easily increase it or make it a second argument to the function.

Proof of work:

Here the macro function you have to import:

REM (F)ind(R)ange(M)atches
Function FRM(range as string)
	Dim out as string
	Dim start as string
	Dim match as boolean 
	Set oConv = ThisComponent.createInstance("")
	Const maxSheetSearchRange = "A1:Z100"
	Set oASheet = ThisComponent.CurrentController.ActiveSheet  
	Set oMRange = oASheet.getCellRangeByName(range)
	start =  oMRange.getCellByPosition( 0, 0 ).String
	Set oSheets = ThisComponent.getSheets()
	For k = 0 To oSheets.getCount() - 1
		Set oSheet = oSheets(k)  
		Set oRange = oSheet.getCellRangeByName(maxSheetSearchRange)
		For i = 0 To oRange.Rows.getCount() - 1             
			For j = 0 To oRange.Columns.getCount() - 1
				Set oCell = oRange.getCellByPosition( j, i )
				if ( oCell.String = start ) then
					match = True 
					For l = 0 To oMRange.Rows.getCount() - 1             
						For m = 0 To oMRange.Columns.getCount() - 1
							Set oMCell = oMRange.getCellByPosition( m, l )
							if ( not (l = 0 and m = 0) ) then
								Set oCell2 = oRange.getCellByPosition( j+m, i+l )
								if ( oCell2.String <> oMCell.String ) then
									match = False
									Exit For
						if (match = False) then
							Exit For
					if (match = True) then
						oConv.Address = oCell.getCellAddress()
						out = out & "Match: " & oConv.PersistentRepresentation & chr(10) 
End Function

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

Have a nice day and let’s (continue to) “Be excellent to each other!”

‘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,


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 …

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 …