Hi!

Not pretty but it works!

I assume there are no blanks in the array and you want
this for each element. My tests were based on a range of
A1:A15. Adjust to suit. In B1 I array entered this formula
with the key combo of CTRL,SHIFT,Enter:

=IF(ISERROR(SMALL(IF(A$1:A$15=A1,ROW(A$1:A$15)),CO UNTIF
(A$1:A1,A1)+1)),"none",SMALL(IF(A$1:A$15=A1,ROW
(A$1:A$15)),COUNTIF(A$1:A1,A1)+1))&","&IF(ISERROR( SMALL(IF
(A$1:A$15=A1,ROW(A$1:A$15)),COUNTIF(A$1:A1,A1)-
1)),"none",SMALL(IF(A$1:A$15=A1,ROW(A$1:A$15)),COU NTIF
(A$1:A1,A1)-1))

Copy down as needed. This returns the actual row numbers
and not the position in the array.

Biff

-----Original Message-----
I have an array with 500+ elements. Assume elements 100,
200, 300, and 400
are equal to 'Sue'. For each occurrence of Sue, I want
to find the row
number for the next occurrence and for the previous
occurrence. At 100, I
get 200 and 'none'. At 200, I get 300 and 100. But when
I get to 300, I get
400 and 100, altho I want 400 and 200. When I am at 300,
is there some way
to have the search start at 299 and search backwards so
that the first match
it comes to is at 200?

--
Bill @ UAMS
.