Can anyone tell me the difference between using Choosecols and Index?

CHOOSECOLS is shorter for multiple columns:

=CHOOSECOLS(A:E,1,3,5)
=HSTACK(INDEX(A:E,,1),INDEX(A:E,,3),INDEX(A:E,,5))

It's trial and error - sometimes INDEX works better, sometimes CHOOSECOLS does. Having more than one way to skin a cat is always useful. There is no definitive answer to your question.

If you always want to select 1 cell, 1 row or 1 column with the formula (and you know the position in the range) the easiest way is to use INDEX.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

CHOOSECOLS always returns an array, not a reference to a range, so it won't work with a function like SUMIF or COUNTIF.

Hi Rorya, thanks for your reply.

but errr.... whats the difference between array and range? Then index is return array or range?

An array is a matrix of values in memory. INDEX may or may not return a range, depending on how it's used. Generally speaking, when applied to a range, it will return a reference to a range. You could for example use:

=SUMIF(INDEX($A:$C,0,1),"a",INDEX(A:C,0,3))

but you can't use:

=SUMIF(CHOOSECOLS(A:C,1),"a",CHOOSECOLS(A:C,3))

Wow. Thats very very good explaination. Does it mean Array might be either Array or Range but Range is not Array?

From what you say, seems like using Index will work 99% of the time.

In Power Query you have List and Table, they are different.
Range = A1:B3
Array= {1,2,3\4,5,6}