#Num error in Array

[Guest]

Hello,

I have the following array in the first cell. The array works great on rows
1-3:
=INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,ROW($1:$5)),ROW(1:1)))


when it reaches the 4th occurences it gives the #num error, below is the
code in the 4th cell or row.
=INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,ROW($1:$5)),ROW(4:4)))

What can I do to fix the array?

 

[Biff]

Hi!

There's nothing wrong with your formula.

Are you sure the 4th occurrence is a match?

What does this return:

=COUNTIF(GCData!AN$1:AN$5,1)

The 4th instance may be a TEXT value?

Biff

 

[Guest]

Biff,

It returns the number "3".

The cells in column AN display the following?
AN3:AN6 1
AN7
AN8 1
AN9
AN10 1
AN11
AN12:AN19 1
.....

 

[Biff]

It returns the number "3".

Ok, if that formula returns 3 that means there isn't a 4th instance in the
range A1:A5 causing the #NUM! error.

If you want to pick up the 4th instance you need to extend your range.

Biff

 

[Domenic]

It looks like you need to adjust the range for Column AN. Assuming that
the formula is entered in AO1 and copied down, try...

=INDEX(GCData!AM$3:AM$19,SMALL(IF(GCData!AN$3:AN$19=1,ROW(GCData!AN$3:AN$
19)-ROW(GCData!AN$3)+1),ROWS(AO$1:AO1)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Guest]

Biff,

I must have something else wrong then becuase it displays an 1 in the 4 row
which would create the 4th instance. Is not reading it as a number somehow
thus causing the error?

 

[Biff]

See Domenic's reply. I think he may have figured it out. Your original post
uses the range of AN1:AN5 but then your follow-up looks like the range is
AN3:AN19. If that doesn't solve the problem post back with the EXACT details
of the ranges you're using AND the EXACT formula you're using.

Biff