Unexpected result

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

=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
>
>
>
>
>
>

Thanks for your input.

Actually, I was just "playing" around with some stuff and couldn't quite
figure out what I was seeing.

Biff

"mangesh_yadav" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Biff,
>
> your question and the solution aaray did not match. Why don't you
> simply use:
> =IF($A$10="X",A1,"") and copy down
> or
> =IF($A$10="X",A1:A5,"") as an array formula by selecting all the five
> cells in the column.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile:
> http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=375888
>

Ok, that makes sense!

Biff

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> =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
>>
>>
>>
>>
>>