Hi Folks!

Can someone explain the result I'm getting:

A1 = 11
A2 = 12
A3 = 13
A4 = 14
A5 = 15

A10 = empty

I want the formula to extract the values in A1:A5 IF A10 = X

Formula: (array entered)

=INDEX(A$1:A$5,SMALL(IF(A$10="X",ROW($1:$5)),ROW(1 :1)))

Copied down 5 cells returns:

11
#NUM!
#NUM!
#NUM!
#NUM!

I should get #NUM! in every cell.

Here's where I don't understand the result of the first cell return of
11....

Evaluating the formula and stepping through:

SMALL(FALSE,{1}) evaluates to SMALL(0,1) = 0

So, =INDEX(A$1:A$5,0)

Returns the value in the first position in the array A1:A5, 11.

=INDEX(A$1:A$5,1) also returns the value in the first position in the array
A1:A5, 11.

I would think that there is no zero position in the array and the formula
should error.

I know that the above #NUM! errors are being generated by the SMALL function
but shouldn't INDEX also generate an error based on position zero?

I'm confiussed on this!

Thanks

Biff