Why does this formula give #N/A

[Asheesh]

Experts - please find the attached...I am getting this #N/A error when I use this array formula...however, when I evaluate the function..I do see that it is working fine..until it reachs index(array, row)..

I do have an alternate formula to get this task done...however, I am wondering what is wrong with it..

Can you please help me spot the error

 

[Somendra Misra]

Hi Asheesh,

What is the purpose of this formula?

Regards,

 

[Asheesh]

Hi Som -

Purpose is to extract the uniques from the list that is in column A and then use these unique values as column headers...

Hope this answers your question..

 

[Somendra Misra]

Well the first problem is in array creation of SMALL function which is having values all 3 or "" (Null string).

2nd you are demanding SMALL number of 1st to 10 where as your array has only 7 numbers so 8th, 9th and 10th small will be #N/A error.

Try changing the formula to as below:

=INDEX($A$2:$A$11,SMALL(IF(MOD((COLUMN())/(TRANSPOSE(COUNTIF($A$2:$A$11,$A$2:$A$11))),1)>0,9.9999E+307,COLUMN()),COLUMN()-2))

Regards,

Regards,