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