Forum Discussion

R_Martin2075's avatar
R_Martin2075
Copper Contributor
Mar 09, 2023
Solved

INDEX Array with OR Function

Hi,

I'm looking for some help adding an 'OR' function to my index array.

 

My first formula looks for attendance below 96%, and the second formula looks for number of times late over 5.

 

=IFERROR(INDEX($R$2:$R$2000,SMALL(IF($BE$2:$BE$2000<=96%,ROW($R$2:$R$2000)),ROW(1:1))-1,1),"")

 

=IFERROR(INDEX($R$2:$R$2000,SMALL(IF($AD$2:$AD$2000>="5",ROW($R$2:$R$2000)),ROW(1:1))-1,1),"")

 

Basically, I want to know if it's possible to combine these two formulas together so that the table is populated with anyone whose attendance is below 96% OR has more than 5 'Lates' recorded?

 

Many thanks in advance!

  • R_Martin2075 

    Does this work?

     

    =IFERROR(INDEX($R$2:$R$2000,SMALL(IF(($BE$2:$BE$2000<=96%)+($AD$2:$AD$2000>=5),ROW($R$2:$R$2000)),ROW(1:1))-1,1),"")

2 Replies

  • R_Martin2075 

    Does this work?

     

    =IFERROR(INDEX($R$2:$R$2000,SMALL(IF(($BE$2:$BE$2000<=96%)+($AD$2:$AD$2000>=5),ROW($R$2:$R$2000)),ROW(1:1))-1,1),"")

    • R_Martin2075's avatar
      R_Martin2075
      Copper Contributor
      That's worked perfectly - thank you so much! 🙂