#Num error in Array
 

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,RO W($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,RO W($1:$5)),ROW(4:4)))

What can I do to fix the array?

#Num error in Array
 

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

"Ben Dummar" wrote in message
...
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,RO W($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,RO W($1:$5)),ROW(4:4)))

What can I do to fix the array?

#Num error in Array
 

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" wrote:

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

"Ben Dummar" wrote in message
...
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,RO W($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,RO W($1:$5)),ROW(4:4)))

What can I do to fix the array?

#Num error in Array
 

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

"Ben Dummar" wrote in message
...
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" wrote:

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

"Ben Dummar" wrote in message
...
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,RO W($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,RO W($1:$5)),ROW(4:4)))

What can I do to fix the array?

#Num error in Array
 

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$1 9=1,ROW(GCData!AN$3:AN$
19)-ROW(GCData!AN$3)+1),ROWS(AO$1:AO1)))

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

Hope this helps!

In article ,
Ben Dummar wrote:

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" wrote:

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

"Ben Dummar" wrote in message
...
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,RO W($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,RO W($1:$5)),ROW(4:4)))

What can I do to fix the array?

#Num error in Array
 

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" wrote:

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

"Ben Dummar" wrote in message
...
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" wrote:

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

"Ben Dummar" wrote in message
...
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,RO W($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,RO W($1:$5)),ROW(4:4)))

What can I do to fix the array?

#Num error in Array
 

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

"Ben Dummar" wrote in message
...
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" wrote:

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

"Ben Dummar" wrote in message
...
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" wrote:

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

"Ben Dummar" wrote in message
...
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,RO W($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,RO W($1:$5)),ROW(4:4)))

What can I do to fix the array?