Error with array formulas in excel

Question

I am having a problem with array formulas - they work properly in one workbook but not in another (same formula different branch office).  When I enter just the formula (not as array) it brings back the correct data but as soon as I convert to an array it returns #NA.  This is the formula {=INDEX(Data!A$2:A$2001,SMALL(IF(Data!$M$2:$M$2001=1,ROW(Data!A2:A$2001)-ROW(Data!A$2)+1),ROWS(Data!A$2:A2)))} .  Does anyone know why this is not working?

Answer 1

Hi Sheeloo

My data set runs from a1 to m2001, row 1 is a header row.  Column m has 12 "1"s in it, however, I have found some #NA fields in column m (invalid data entered in the source spread sheet).  When I clear these the array formula works properly.

Thanks for your help.

Answer 2

I got that.

I entered it as an Array and it worked as long as Column M in Data sheet had at least one 1.

I also entered numbers in Col A

Answer 3

Thanks for your feedback. Happy to help.

I usually try any formula with least possible data so that I can find out any issues. Once I am confident that the formula is correct then it is easier to find any issues with larger data set.

Answer 4

Hi Sheeloo  - I know that the formula is correct, as a formula it returns the correct information.  However, when I enter it as an array (CSE) it changes to #NA & I cannot understand why.

Answer 5

Formula is correct... one of the cells in the range Data!$M$2:$M$2001 must be equal to 1 for this to work...