Ok, that makes sense!

Biff

"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:429eba0b$0$27358$e4fe514c@news.xs4all.nl...
> =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
>>
>>
>>
>>
>>