Logical Operators within formulas

[aidan5800]

Hi all,

Could someone help with this please. I have as array with a formula that contains a greater than function as shown below. I'd like this formula to change to a less than or greater than operator dependant on the operator in another cell but i'm not sure how to reference another cell with a logical operator.


=IFERROR(INDEX('Sheet1'!$B$1:$B$100,SMALL(IF(INDIRECT('Sheet1'!$B$1:$B$2)>$C$3,ROW(INDIRECT('Sheet1'!$B$1:$B$2))),ROW(1:1))),"")


perhaps something like?


=IFERROR(INDEX('Sheet1'!$B$1:$B$100,SMALL(IF(INDIRECT('Sheet1'!$B$1:$B$2)&D1&$C$3,ROW(INDIRECT('Sheet1'!$B$1:$B$2))),ROW(1:1))),"")

where D1 contains either a < or a >.

[Bo_Ry]

Please try
=IFERROR(INDEX('Sheet1'!$B$1:$B$100,SMALL(IF(INDIRECT('Sheet1'!$B$1:$B$2)<>$C$3,ROW(INDIRECT('Sheet1'!$B$1:$B$2))),ROW(1:1))),"")

[aidan5800]

Sorry this isn't what i'm looking for. I need a selection between a greater than or less than operator within the formula so it will need to point to another cell containing either of those, but its how to embody that within the formula. C3 just contains the value to compare against.

Thanks.

[Bo_Ry]

Maybe try

=IFERROR(INDEX(Sheet1!$B$1:$B$100,SMALL(IF(IF(D1="<",INDIRECT(Sheet1!$B$1:$B$2)<$C$3,INDIRECT(Sheet1!$B$1:$B$2)>$C$3),ROW(INDIRECT(Sheet1!$B$1:$B$2))),ROW(1:1))),"")

[aidan5800]

Thanks, I wanted to avoid making the formula larger by embedding another IF hence if you could simply point to another cell using &D1&>$C$3... I'm not sure why that doesn't work though.

[Bo_Ry]

> < = are operate that cannot use with & as it becomes text but Countif condition can use with text.

perhaps
=IFERROR(INDEX(Sheet1!$B$1:$B$100,SMALL(IF(COUNTIF($C$3,D1&INDIRECT(Sheet1!$B$1:$B$2)),ROW(INDIRECT(Sheet1!$B$1:$B$2))),ROW(1:1))),"")