Extract all rows that contain a value between this and that
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?
Answer:
Array formula in H2:
How to create an array formula
- Select cell H2
- Type above array formula
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys
- Select cell H2
- Copy (Ctrl + c)
- Select cell I2:J2
- Paste (Ctrl + v)
- Select cell H2:J2
- Copy
- Select cell range H3:J5
- 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



















There appears to be an error in your formula.
Row 2 of the list has values of North, 1, 1
The "1" falls in the extract range but it is not included in the results.
Thank you!
I have updated the array formula and the attached file.
this is very very useful, and thanks so much.
q: how would i change the formula, if the "ITEM" is in column A, the "VALUE" is in column B and the "COMPANY" is in column C?
and what is i have another column "NAME" in between columns and ITEM and VALUE
James,
see this blog post: http://www.get-digital-help.com/2009/09/28/extract-all-rows-from-a-range-that-meet-criteria-in-one-column-in-excel/
Is it possible to have the data on one sheet and the sorted information on another sheet using this formula?
Paul,
Yes!
See file!
Extract all rows that contain a value between this and that.xlsx
Thanks thats great. Is it possible to sort the data in the array eg if the search criteria was dates, nearest dates first. If not how can I copy the data from the array to sort using sort and filter.
Thank you very much!
Paul,
How to copy data from the array
1. Select the cell range
2. Copy (Ctrl + c)
3. Right click on destinaton cell
4. Click "Paste Special..."
5. Click "Values"
6. Click OK!
Now you can sort and filter copied values!