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)

Download excel template

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

Functions in this article:

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