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

Related posts:

Extract all rows from a range that meet criteria in one column in excel

Merge matching rows in excel (text values)

Match two criteria and return multiple rows in excel

Easily identify groups of duplicate rows

Filter duplicate rows in excel 2007