Help with @MATCH

I use the @MATCH function to find a value in a column with hundreds of rows. 
In order to find the second and subsequent occurances of that value I have 
to change the beginning value of the block each time. How can this process 
be done automatically? Thank you for any help.

This could be done by adding an extra column to keep track of the 
offsets found but a Notebook Data Query might be a better route.

Dave F. wrote:
> I use the @MATCH function to find a value in a column with hundreds of
rows. 
> In order to find the second and subsequent occurances of that value I have

> to change the beginning value of the block each time. How can this process

> be done automatically? Thank you for any help.

Let's say your data is in A1..A1000, and D1 contains the value you're looking 
for.  Create a helper column starting in B1:

   @ARRAY((A1..A1000=D1)*@ROW(A1..A1000)+(A1..A1000<>D1)*1000000)

Then the occurrences of the value of interest, by row number, are given by:

  1st:  @SMALLEST(B1..B1000,1)
  2nd:  @SMALLEST(B1..B1000,2), etc.

Cheers,
Uli

Nicely done Uli!

Uli wrote:
>   @ARRAY((A1..A1000=D1)*@ROW(A1..A1000)+(A1..A1000<>D1)*1000000)
> 
> Then the occurrences of the value of interest, by row number, are given
by:
> 
>  1st:  @SMALLEST(B1..B1000,1)
>  2nd:  @SMALLEST(B1..B1000,2), etc.
> 

Using Uli's method:

H6..H11 with H7..H11=Grade ranged name=
Grade
92
84
75
67
84

G6..G11=
Name
Fred
Mary
Reno
Anne
John

MG=Named range with a value of say 84.

I7=@ARRAY((Grade=MG)*@ROW(Grade))
J7=@IF(I7<>0,@INDEX(A:G,0,I7-1),"")

J7 is copied down to J11.

Therefore, you will get Mary and John in column J.