Ok, that makes sense!

Biff

"Aladin Akyurek" wrote in message
...
=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