Reverse MATCH Function
 

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

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
.

Assuming the names in column A, in a column on the right, say K, put this
formula

=IF(A1="Sue",COUNTIF(A$1:A1,"Sue"),"")

and copy it down. With the example you give, the formula should return 3 at
row 300. Then you can use

=MATCH(K300-1,$K$!:$K$500,0)

to get the row above and

=MATCH(K300+1,$K$!:$K$500,0)

to get the row below.

Of course, if "Sue" is just an example, and you really want to search for
other names as well, this isn't going to work.

On Tue, 8 Mar 2005 14:29:06 -0800, "BillCPA" <Bill @ UAMS wrote:

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?