Display row numbers of a range

[Cap]

I'd like to be able return the the row numbers (started at
row - ends at row) from a list of names that are in D2 to
the end possibly. The list will always be sorted!

(E.g. In B2 I'd like to type in Joe; and in Column D Joe
is found starting at row 371 and the last row with Joe is
806. It does not matter if two cells are needed to show
the two numbers.)

I'm not sure how to do this!

TIA

 

[Jason Morin]

First row (formula in J1):

=MATCH(B1,D,0)

Last row:

K1: =J1+COUNTIF(D,B1)-1

HTH
Jason
Atlanta, GA

 

[Peo Sjoblom]

One way

this will give you the cell address of the first and the last

first

=CELL("address",INDEX(D210000,MATCH(B2,D210000,0)))

last


=CELL("address",INDEX(D110000,MAX((D210000=B2)*(ROW(D210000)))))

the latter entered with ctrl + shift & enter
also note that for the latter regardless where the data starts index needs
to start in row 1 (D1) or else you have to offset the number of rows where
the data starts




Regards,


Peo Sjoblom