=INDEX(A1:A5,0)

which is identical to the full version:

=INDEX(A1:A5,0,1)

means all of the rows of A1:A5.

The formula cell will house the result given your sample:

={11;12;13;14;15}

with the topleft cell displaying.

The foregoing also holds for:

=INDEX(A1:A5,{0})

Intermezzo: Invoke =INDEX($A$1:$A$5,0) in a cell in the same worksheet
you want to data validate as Source. That cell will show you all of the
values from A1:A5.

BTW, ROW(1:1) in

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

makes the formula non-robust (therefore incorrect) against row
insertions before the formula row.

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