Can I use IF(OR function to extract data from a table with an array formula

[Caesar]

Hi all,

I have an array formula to extract data from an excel table with two criteria. The sample file has the array formula I have written and it works as intended. The formula is as follows:
=INDEX(Movements!A:A,SMALL(IF((Movements!$A$6:$A$45=Sheet1!$A$4)*(Movements!$G$6:$G$45=Sheet1!$C$4),ROW(Movements!$A$6:$A$45)),ROW(A$1)))

However, I am trying to change the IF function to an IF(OR function but it does not work. The new array formula I am using is as follows:

=INDEX(Movements!A:A,SMALL(IF(OR((Movements!$A$6:$A$45=Sheet1!$A$4,Movements!$A$6:$A$45=Sheet1!$B$4))*(Movements!$G$6:$G$45=Sheet1!$C$4),ROW((A$1)))

Any help on where I am going wrong is appreciated.

Thanks all. I realized here the mistake was and have corrected the formula. The second bracket had to move to between the IF and OR. It now works as intended.

=INDEX(Movements!A:A,SMALL(IF((OR(Movements!$A$6:$A$45=Sheet1!$A$4,Movements!$A$6:$A$45=Sheet1!$B$4))*(Movements!$G$6:$G$45=Sheet1!$C$4),ROW((A$1)))

 

[Peter Bartholomew]

Without the test workbook, I cannot really see what is going on (I am not very good at reading formulas with direct referencing) but I am slightly puzzled by the use of OR within an array formula.

= OR(Movements!$A$6:$A$45=Sheet1!$A$4,
Movements!$A$6:$A$45=Sheet1!$B$4)

will aggregate the test for all 40 rows into one Boolean. I would more usually expect to see

= (Movements!$A$6:$A$45=Sheet1!$A$4) +​

(Movements!$A$6:$A$45=Sheet1!$B$4) ​

which yields an array of 40 results [0, 1].​

 

[Caesar]

Without the test workbook, I cannot really see what is going on (I am not very good at reading formulas with direct referencing) but I am slightly puzzled by the use of OR within an array formula.

= OR(Movements!$A$6:$A$45=Sheet1!$A$4,
Movements!$A$6:$A$45=Sheet1!$B$4)

will aggregate the test for all 40 rows into one Boolean. I would more usually expect to see

= (Movements!$A$6:$A$45=Sheet1!$A$4) +​

(Movements!$A$6:$A$45=Sheet1!$B$4) ​

which yields an array of 40 results [0, 1].​

Thanks for the response Peter. I have used OR in the formula as any one of the criteria from A4 or B4 must be met and then the third criteria in C4 also must be met. The formula results are as per what I wanted.