Or, you could use a UDF. Using the previous assumptions you would enter it
in B1

=findrows(Sheet1!$A$1:$A$7,A1)


Function FindRows(rngData As Range, strCriteria As String) As String
Dim rngCell As Range

For Each rngCell In rngData.Cells
If rngCell.Value = strCriteria Then _
FindRows = FindRows & " " & CStr(rngCell.Row)
Next rngCell

If FindRows < "" Then _
FindRows = Replace(LTrim(FindRows), _
" ", ", ", 1, -1, vbTextCompare)

End Function



"JMB" wrote:

If you want all of the rows a particular name appears in and assuming your
source data of names is in Sheet1!A1:A7, Sheet2 cell A1 contains the name for
which you are searching, you could enter this formula in cell B1 (of Sheet2)
and copy across until the formula returns blanks. The formula is an array
formula, you must confirm it with Control+Shift+Enter.

Modify the ranges and cell references as needed.

IF(SUM(--(Sheet1!$A$1:$A$7=$A1))<COLUMN()-COLUMN($B1)+1,"",SMALL(IF(Sheet1!$A$1:$A$7=$A1,ROW (INDIRECT(ROW(Sheet1!$A$1)&":"&ROWS(Sheet1!$A$1:$A $7))),""),COLUMN()-COLUMN($B1)+1))

"Val" wrote:

I have a list of data containing names (30,000+) that I need to look through.
Can Excel tell me which rows a name appears using a formula using something
like CELL(not using find or filter)? I need to leave them in their mixed-up
order. My boss wants the exact row numbers of each name and looking up 2,500
names manually will take a really long time. If Excel can do this, how do I
do it?