Hi ,
I'll try to explain.
Let us start with what the INDEX function expects.
The INDEX function expects a range as a first parameter , and an index number into this range as the second parameter ; if we have a range of 10 cells , where ever they may be located in a worksheet , if we want the 5th cell from this range , we can say =INDEX(range of 10 cells , 5).
This is a general construct , and every formula that uses the INDEX function , can finally be reduced to this construct. I have colored the two parts in different colors so that you can see the equivalence in Misra's formula.
If we take Misra's formula , it is :
=INDEX(zipcodesbyemployee!$C$2:$C$5 ,SMALL(IF(ISNUMBER(SEARCH(C2,zipcodesbyemployee!$A$2:$A$5)),IF(D2=zipcodesbyemployee!$B$2:$B$5,ROW(zipcodesbyemployee!$C$2:$C$5)-ROW(zipcodesbyemployee!$C$2)+1)),1))
So what the SMALL function does , in the simplest terms , is it returns a particular number from a list of numbers.
------------------------------------------------------------------------------------------------------------------------
The SMALL function , can again be reduced to its basic form as :
SMALL(list of items , which smallest number)
If the second parameter in the above construct for SMALL is 1 , it will return the smallest number from the list of items ; if it is 2 , it will return the second smallest number , and so on.
In Misra's formula , the second parameter is 1 , which means it will return the smallest number from the list of items.
------------------------------------------------------------------------------------------------------------------------
Thus , the first parameter list of items is being created by the following segment :
IF(ISNUMBER(SEARCH(C2,zipcodesbyemployee!$A$2:$A$5)),IF(D2=zipcodesbyemployee!$B$2:$B$5,ROW(zipcodesbyemployee!$C$2:$C$5)-ROW(zipcodesbyemployee!$C$2)+1))
This is a straightforward IF statement , which tests for a condition , and if true , returns a particular value ; since this entire formula is being array-entered , using CTRL SHIFT ENTER , the IF statement is going to operate on an array of items , and return an array as a result.
The syntax of the IF statement is :
=IF(test for condition , return value if test evaluates to TRUE , return value if test evaluates to FALSE)
Here , the test for condition is formed by 2 IF statements , which have been highlighted above.
------------------------------------------------------------------------------------------------------------------------
What these 2 IF statements return if the test evaluates to TRUE is an array given by this segment :
ROW(zipcodesbyemployee!$C$2:$C$5)-ROW(zipcodesbyemployee!$C$2)+1
This segment is a very commonly used one , which can be more generally written as :
=ROW(range) - MIN(ROW(range)) + 1
where range can be any section of cells within a column ; the above construct will in all cases return an array of numbers from 1 till the number of elements in the range. Thus , if the range extends from J17 through J55 , the number of elements is 55 - 17 + 1 = 39.
Thus , the above construct , which will now be :
=ROW(J17:J55) - MIN(ROW(J17:J55)) + 1
will return the array :
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39}
This works this way :
The first segment ROW(J17:J55) returns the array :
{17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55}
From this array , we subtract the minimum value , which in this case is 17.
The array now becomes :
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38}
Adding 1 to this results in the final array of numbers from 1 through 39.
------------------------------------------------------------------------------------------------------------------------
The SEARCH function does a very simple job ; it looks for a given item in a list of items , and if found returns the position where this given item is located in the list of items ; if not found , it returns the error value #VALUE!.
So , suppose we have a list of items covering 5 cells , and if the looked for item is found in the first position in the 4th item among the 5 , the SEARCH function will return an array :
{#VALUE!;#VALUE!;#VALUE!;1;#VALUE!}
Suppose in the list of 5 items , the looked for item is found in the 7th position , in the 3rd item , the returned array will be :
{#VALUE!;#VALUE!;7;#VALUE!;#VALUE!}
Suppose in the list , there are 2 cells which contain the looked for value , in the 7 position in the 3rd item and the 1st position in the 4th item , the returned array will be :
{#VALUE!;#VALUE!;7;1;#VALUE!}
Now , if we wrap the ISNUMBER function around this array , what we get is an array of TRUE and FALSE values , TRUE where the array contains a number , and FALSE where it contains the error value #VALUE!.
{FALSE;FALSE;TRUE;TRUE;FALSE}
------------------------------------------------------------------------------------------------------------------------
Thus , if we have a construct such as :
SMALL(IF(ISNUMBER(SEARCH(given item , list of 5 items)) ,
ROW(list of 5 items) - MIN(ROW(list of 5 items)) + 1) , 1)
and suppose the given item is found in two places within the list , the position where it is found in those items not being relevant , and suppose the two places are the 2nd and the 5th , the segment in RED , will return the array :
{FALSE;TRUE;FALSE;FALSE;TRUE}
The segment in GREEN will return the array :
{1;2;3;4;5}
Put together , these will return the array :
{FALSE;2;FALSE;FALSE;5}
where the FALSE values are retained in their positions , while where ever the TRUE value was present , it will now be replaced by the row number from the second array.
Putting the SMALL function around this array , and using 1 as the second parameter will return the value 2 , since the SMALL function ignores FALSE values , and out of the two numbers 2 and 5 , 2 is the smaller.
------------------------------------------------------------------------------------------------------------------------
Narayan
can't you add one more criteria function role in it?