Hi Ananthram ,
This construct is used to form a single multiple column and multiple row range out of non-contiguous ranges.
For example , if you want to do a matrix multiplication of say a 4 x 2 matrix , by another 2 x 4 matrix , where say the first matrix has non-contiguous ranges ; you can use this construct to form the matrix.
Let us say you have three ranges : G3:G6 , I3:I6 and K3:N4.
The following formula , entered over a 4 x 4 range , and entered as an array formula will do the matrix multiplication.
=MMULT((CHOOSE({1,2},G3:G6,I3:I6)),K3:N4)
In the example you have given , the first parameter of the VLOOKUP function is a single value , returned by the LARGE( ... ) function. The second parameter of the VLOOKUP function is supposed to be a range ; since you have two non-contiguous ranges , the CHOOSE( ... ) function combines them both into one range having two columns and 20 rows.
What you are doing with the LARGE function is retrieving the third highest value in the range D1:D20 ; since you wish to retrieve the corresponding value in the column B , which is to the left of column D , you do a VLOOKUP of the value in column D and then use 2 in the third parameter to retrieve the corresponding value from the second column of the range , which because you have used CHOOSE({2,1},....) is column B.
The composite range is formed by taking D1 , then B1 , then D2 , then B2 and so on :
{D1,B1;D2,B2;D3,B3;....;D19,B19;D20,B20}
Narayan
P.S. I realized that your question of why {1} does not work was not addressed ; so here goes.
When you use {1} , what you are doing is create an array of values from the range mentioned first , which in your case is D1:D20 ; so when you use 2 as the third parameter , then there is no second column to fetch a value from ; you should get a #REF! error.
