Bob,

Nothing sour in that! :-) I have to agree, yours does look better! And
I agree that "general purpose" (Although I would opt to tackle this in
code as Harlan suggests.

The formula I posted isn't sensitive to where the data starts, at least
based on a couple of quick tests, but it is sensitive to where *it*
starts. That's probably what you meant. When I modify it to be more
general purpose (regarding its initial cell placement), it gets a bit
uglier. Here it is with a starting cell of J7:

=IF(COUNTIF(Salesmen,"Bob")<ROW()-ROW($J$7)+1,"",MATCH("Bob",OFFSET(Salesmen,OFFSET( J7,-1,0),0),0)+OFFSET(J7,-1,0))

Anyway, thanks for your help. It's appreciated.

Regards,

Gary
(less two thru five for direct)

Bob Phillips wrote:
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)