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?




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

Recommended article

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

Lookup with criteria and return records.

Comments(35) Filed in category: Excel, Filter 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

Learn the basics of Excel arrays

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

Comments(2) Filed in category: Count values, Excel

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