Hi ,
The reason for this number , which is one more than the maximum number of rows in an Excel worksheet , is as follows :
If we remove all portions of the formula which are not pertinent to the topic under discussion , we are left with the following :
INDEX(($A$2:$A$8=$B$10)*(MATCH(ROW($B$2:$B$8),ROW($B$2:$B$8)))+($A$2:$A$8<>$B$10)*1048577,)
There are 3 parts to this formula , which are colored red , blue and green.
1. The portion colored red returns an array of values TRUE/FALSE , which when multiplied by the portion colored blue changes to numeric values of 1 / 0 ; 1 where the data range $A$2:$A$8 has values that equal the value in $B$10 , and 0 otherwise.
2. The portion colored blue merely returns an array of values , starting with 1 , and going up to the number of elements in the range that is used ; in this case the range $B$2:$B$8 has 7 elements. Thus the array returned by this portion will be the array :
{1;2;3;4;5;6;7}
3. The portion colored green returns an array of values TRUE/FALSE , TRUE where the data range $A$2:$A$8 has values that do not equal the value in $B$10 , and 0 otherwise.
Because of the multiplication sign between portion 1 and portion 2 , that operation will be carried out first ; this will result in an array of values , which has the row number where values in the data range $A$2:$A$8 equal the value in $B$10 , and 0 otherwise.
Thus , an example array might be :
{1;0;0;0;5;0;7}
which means only the first element , the fifth element and the last element in the data range $A$2:$A$8 equal the value in $B$10.
In this same case , the third portion would have returned an array :
{0;1048577;1048577;1048577;0;1048577;0}
where the 0 is in those positions where the value in the data range $A$2:$A$8 equals the value in $B$10 , and 1048577 in those positions where the value in the data range $A$2:$A$8 does not equal the value in $B$10.
Adding these two arrays results in the array :
{1;1048577;1048577;1048577;5;1048577;7}
The highlighted values will then be returned by the SMALL function.
Thus , even if your data range were to include the last row in an Excel worksheet , and if the value in that cell were to equal the value in $B$10 , the corresponding value in the array will be 1048576 , which can still be returned by the SMALL function.
Narayan
