SOLVED

INDEX Array with OR Function

Copper Contributor

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!

2 Replies
best response confirmed by VI_Migration (Silver Contributor)
Solution

@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),"")

That's worked perfectly - thank you so much! :)
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@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),"")

View solution in original post