Help with a formula...

[dixiebritt]

I have a formula that i use to pull out clients names that state "yes" if they are new. The formula has been working for a while and now it is not. On my very first cell, where I have the formula:
=IF(ROW(D1)>$M$2,"",INDEX($D$2:$D$8000,SMALL(IF($E$2:$E$8000="yes",ROW($D$2:$D$8000)-ROW($D$2)+1),ROW(D1))))
results in the correct result

The next cell, yeilds #NUM ! as the result - instead of the actual company name and it does this for all other cells, where this is the case, instead of listing the client name.
=IF(ROW(D3)>$M$2,"",INDEX($D$2:$D$8000,SMALL(IF($E$2:$E$8000="yes",ROW($D$2:$D$8000)-ROW($D$2)+1),ROW(D3))))

Do you have any solutions on why this would happen?

 

[RoryA]

Did you array enter the formula using Ctrl+Shift+Enter?

 

[Gerald Higgins]

Hi, not sure what this formula is meant to do, but is it actually an array formula ?
Does the first instance of the formula have curly brackets round it like this
{=IF(ROW.......(D1))))}

And is the second instance missing the curly brackets ?

I don't know what your source data looks like, but when I turn both versions into array formulas, I get results that look like they might be right, not #NUM .
Do you know how to turn a formula into an array formula ?

 

[Legacy 436357]

The array works as long as there is data in the columns. You can add to the formula like this for blanks:

=IFERROR(IF(ROW(D1)>$M$2,"",INDEX($D$2:$D$8000,SMALL(IF($E$2:$E$8000="yes",ROW($D$2:$D$8000)-ROW($D$2)+1),ROW(D1)))),"")

Press CTRL+SHIFT+ENTER to make it an array.

 

[dixiebritt]

No curly brackets on this formula. When I put them in, nothing happens. Can you tell me how to turn this back into an array formula?

 

[gaz_chops]

Press F2 to edit cell, then press CTRL+SHIFT+ENTER to make it an array.

 

[dixiebritt]

yes that worked - thank you