That seems like two right answers, 2 and 4. Is the "right" answer an array of numbers? Are you only looking for duplicate 3's? Do you want the number of rows between consecutive duplicates? Or the max difference between any two 3's.
Ken

On Tuesday, July 24, 2012 7:56:09 AM UTC-4, JAgger1 wrote:
I found this formula and it works well,

=ABS(MATCH(B1,A1:A8,0)-MATCH(B2,A1:A8))-1

By entering the value 3 in B1 and B2 I get my answer of 4.

Now if I have a larger range of values, with duplicates,

In column A1:A10 I have the following numbers.


1
3
6
9
6
7
3
2
4
3

anyone know how to modify this formula so I can get a correct answer
of 4 (A2 - A7) and 2 (A7 - A10)

I would like to have a formula that would work with a set of 100
(A1:A100) numbers with multiple duplicates, than