Author: Oscar Cronquist Article last updated on October 27, 2017

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

The picture above shows you the formula result in cell range B20:C25. The numerical search range is 0 to 3.

Array formula in B20:

=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))

Watch a video where I explain the formula

Recommended article

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

Lookup with criteria and return records.

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

Recommended article

How to enter an array formula

Array formulas allows you to do advanced calculations not possible with regular formulas.

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:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

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

returns the column number of a reference