Hate to sound like a sourpuss, because I do agree a non-array is better than
an array if you can get it, but there is a fundamental aspect of mine that
yours doesn't cover (as presented).

If mine is adapted to a named range like yours, it looks better :-)

=IF(ROW(Sheet1!A2)-ROW(Salesmen)+1>(COUNTIF(Salesmen,"Bob")),"",SMALL(IF(Sal
esmen="Bob",ROW(Salesmen),""),ROW(Sheet1!A2)-ROW(Salesmen)+1))

The main point though is that yours works fine if the data starts in row 1.
Mine works even if the salesmen data starts in row 199. The printout
formula needs to still start the index at row 1, but that apart it is
resilient.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GaryDK" <megafour712345@comcast.net> wrote in message
news:1111948507.188665.141440@g14g2000cwa.googlegroups.com...
> Regarding the original question and Bob's solution, here's a simpler
> formula that is not an array formula.
>
> I named the salesmen range "Salesmen", including the header cell if
> there is one. On Sheet2, leave cell A1 blank, and enter the following
> formula in cell A2. Then copy it down through A101:
>
>
=IF(COUNTIF(Salesmen,"Bob")<ROW()-1,"",MATCH("Bob",OFFSET(Salesmen,A1,0),0)+
A1)
>
> This will also get you the row numbers, then follow Bob's solution
> starting in B2.
>
> Gary
>