### How to filter numbers inside ranges in column E and F

Array formula in A2:

=SMALL(IF(FREQUENCY(IF((COLUMN(\$A1:\$U1)<=End)*(COLUMN(\$A1:\$U1)>=Start), COLUMN(\$A1:\$U1), ""), COLUMN(\$A1:\$U1))>0, ROW(\$1:\$21), ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell A2 and paste it down as far a sneeded.

Alternative array formula in A2:

=SMALL(IF((COUNTIF(Start, "<="&ROW(\$1:\$21))+COUNTIF(End, ">="&ROW(\$1:\$21)))>(ROWS(Start)), ROW(\$1:\$21)), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell A2 and paste it down as far a sneeded.

### How to filter numbers outside ranges in column E and F

Array formula in B2:

=SMALL(IF(FREQUENCY(IF((COLUMN(\$A1:\$U1)>End)+(COLUMN(\$A1:\$U1)<Start), COLUMN(\$A1:\$U1), ""), COLUMN(\$A1:\$U1))=ROWS(Start), ROW(\$1:\$21), ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell B2 and paste it down as far a sneeded.

Alternative array formula in B2:

=SMALL(IF((COUNTIF(Start, "<="&ROW(\$1:\$21))+COUNTIF(End, ">="&ROW(\$1:\$21)))<(ROWS(Start)+1), ROW(\$1:\$21)), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell B2 and paste it down as far a sneeded.

Named ranges

Start (E2:E5)
End (F2:F5)

numbers inside range.xls
(Excel 97-2003  Workbook *.xls)

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

ROWS(array) returns the number of rows in a reference or an array

COLUMN(reference) returns the column number of a reference