Formula explonation

[Zhanibek]

I need someone to help me to expain the meaning of this formula especially more explonation on part which highlited with red colour ; ( {=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX('Main data'!$A$3:$A$37,SMALL(IF('Main data'!$K$3:$K$37="x",ROW('Main data'!$A$3:$A$37)-ROW('Main data'!$A$3)+1),ROWS($A$2:$A2))))} ).

Here I have attachment for example on this formula. This formula on Sheet2 in Cell B2.
Thanks in advanced.

 

[Somendra Misra]

Hi Zhanibek,

Welcome to Chandoo.org forum.

The formula can be reduced to :
=INDEX('Main data'!$A$3:$A$37,SMALL(IF('Main data'!$K$3:$K$37="x",ROW('Main data'!$A$3:$A$37)-ROW('Main data'!$A$3)+1),ROWS($A$2:$A2)))

Basically this formula is extracting data from column A on Sheet Main Data, from the row where you have x in column K on the same sheet.

The red portion is giving you row number.

ROW('Main data'!$A$3:$A$37)-ROW('Main data'!$A$3)+1 --- > This portion of the formula generates an array of {1;2;3;4;5;6;7.........33;34;35} . Now if any row will have x in column K a row number is alloted. Thus small function will have an array of numbers from 1 to 35 based on were is x.

Now as you drag down the formula ROWS($A$2:$A2) will generate 1;2;3 as it has a dynamic expanding range in it.

So SMALL function will give the small number of array formed in it in order and INDEX function will treat it as row number for array Main Data!$A$3:$A$37.

Thus we will get results.

Regards,