Question: I have a list and I want to filter out all rows that has a value (Column C) that is bigger or equal than zero and smaller or equal than three?

extract-rows

Answer: 

extract-rows2

Array formula in H2:

=INDEX($A$2:$C$11, SMALL(IF(($F$1=$C$2:$C$11), MATCH(ROW($B$2:$B$11), ROW($B$2:$B$11))), ROW(A1)), COLUMN(A1))

How to create an array formula

  1. Select cell H2
  2. Type above array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys
How to copy array formula
  1. Select cell H2
  2. Copy (Ctrl + c)
  3. Select cell I2:J2
  4. Paste (Ctrl + v)
  5. Select cell H2:J2
  6. Copy
  7. Select cell range H3:J5
  8. Paste

Download excel sample file for this tutorial
Extract all rows that contain a value between this and that.xlsx
(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

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

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

ROW(reference) returns the rownumber of a reference

COLUMN(reference)
returns the column number of a reference