INDIRECT Function In An Array Formula Getting a #REF Error

[excelbytes]

Here is my formula:
=IFERROR(INDEX(Holdings!$D$2:$D$7,SMALL(IF((INDIRECT("Holdings!$W$2:$W$7"&C13&"search!$D$13")),ROW(Holdings!$D$2:$D$7)-MIN(ROW(Holdings!$D$2:$D$7))+1),ROWS($D$7:$D$7))),"")
Holdings!$W$2:$W$7 is a series of percentages
C13 is the ">" symbol
search!$D$13 is 5%
When I highlight the ref_text for the INDIRECT function and hit F9, I get INDIRECT("Holdings!$W$2:$W$7>search!$D$13")
However, when I highlight (INDIRECT("Holdings!$W$2:$W$7"&C13&"search!$D$13") and hit F9 I get a #REF error.
What am I missing?

 

[jasonb75]

Indirect is only meant to produce ranges, not formulas / calculations. Checking if one value is greater than another is a calculation so is not valid syntax for the indirect function.

 

[jasonb75]

Not entirely sure what the full purpose of your formula is, but assuming that C13 would be either > or < you would need to use something like this.
More options in C13 will mean a more complex formula.

=IFERROR(IF(C13=">",AGGREGATE(15,6,Holdings!$D$2:$D$7/(Holdings!$W$2:$W$7>search!$D$13),ROWS(D$7:D$7)),AGGREGATE(15,6,Holdings!$D$2:$D$7/(Holdings!$W$2:$W$7<search!$D$13),ROWS(D$7:D$7))),"")

 

[excelbytes]

The reason I tried this was to be able to change the ">" symbol to =, or <, or ... Is there an alternative approach?

 

[jasonb75]

Try (untested)

=IFERROR(IF(C13=">",AGGREGATE(15,6,Holdings!$D$2:$D$7/(Holdings!$W$2:$W$7>search!$D$13),ROWS(D$7:D$7)),IF(C13="<",AGGREGATE(15,6,Holdings!$D$2:$D$7/(Holdings!$W$2:$W$7<search!$D$13),ROWS(D$7:D$7)),AGGREGATE(15,6,Holdings!$D$2:$D$7/(Holdings!$W$2:$W$7=search!$D$13),ROWS(D$7:D$7)))),"")