Array Formulas
Cell | Formula |
---|
A2 | {=IF(ROWS(A$2:A2)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A2))))} |
---|
B2 | {=IF(ROWS(A$2:A2)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A2))))} |
---|
C2 | {=IF(ROWS(A$2:A2)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A2))))} |
---|
A3 | {=IF(ROWS(A$2:A3)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A3))))} |
---|
B3 | {=IF(ROWS(A$2:A3)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A3))))} |
---|
C3 | {=IF(ROWS(A$2:A3)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A3))))} |
---|
A4 | {=IF(ROWS(A$2:A4)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A4))))} |
---|
B4 | {=IF(ROWS(A$2:A4)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A4))))} |
---|
C4 | {=IF(ROWS(A$2:A4)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A4))))} |
---|
A5 | {=IF(ROWS(A$2:A5)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A5))))} |
---|
B5 | {=IF(ROWS(A$2:A5)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A5))))} |
---|
C5 | {=IF(ROWS(A$2:A5)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A5))))} |
---|
A6 | {=IF(ROWS(A$2:A6)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A6))))} |
---|
B6 | {=IF(ROWS(A$2:A6)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A6))))} |
---|
C6 | {=IF(ROWS(A$2:A6)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A6))))} |
---|
A7 | {=IF(ROWS(A$2:A7)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A7))))} |
---|
B7 | {=IF(ROWS(A$2:A7)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A7))))} |
---|
C7 | {=IF(ROWS(A$2:A7)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A7))))} |
---|
A8 | {=IF(ROWS(A$2:A8)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A8))))} |
---|
B8 | {=IF(ROWS(A$2:A8)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A8))))} |
---|
C8 | {=IF(ROWS(A$2:A8)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A8))))} |
---|
A9 | {=IF(ROWS(A$2:A9)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A9))))} |
---|
B9 | {=IF(ROWS(A$2:A9)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A9))))} |
---|
C9 | {=IF(ROWS(A$2:A9)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A9))))} |
---|
A10 | {=IF(ROWS(A$2:A10)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A10))))} |
---|
B10 | {=IF(ROWS(A$2:A10)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A10))))} |
---|
C10 | {=IF(ROWS(A$2:A10)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A10))))} |
---|
A11 | {=IF(ROWS(A$2:A11)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A11))))} |
---|
B11 | {=IF(ROWS(A$2:A11)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A11))))} |
---|
C11 | {=IF(ROWS(A$2:A11)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A11))))} |
---|
<thead>
</thead><tbody>
</tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself |