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: Yellow cells are user input cells.
Here is the formula in H2:J12
=IF(SUM(IF(($C$2:$C$11<=$F$2)*($C$2:$C$11>=$F$1), 1, 0))<ROW()-1, "", INDEX($A$2:$C$11, SMALL(IF(($C$2:$C$11<=$F$2)*($C$2:$C$11>=$F$1), ROW($C$2:$C$11)-1, ""), ROW()-1), COLUMN()-7)) + Ctrl + Shift + Enter
Download excel sample file for this tutorial.
Extract all rows that contain a value between this and that.xls
(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
SUM(number1,[number2],)
Adds all the numbers in a range of cells
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
- Filter unique rows and sort by date using array formula in excel
- Filter duplicate rows and sort by date using array formula in excel
- Extract numbers and text from a range using array formula in excel
- Match two criteria and return multiple rows in excel
- Extract dates and adjacent value in a range using a date critera in excel
- Extract cell values in a range using a criterion in excel
- Sequencing and numbering of batches in excel
- How to extract a list of duplicates of a column in excel
- Extract unique values from a range using array formula in excel




September 26th, 2009 at 8:37 pm
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.
September 26th, 2009 at 9:28 pm
Thank you!
I have updated the array formula and the attached file.
September 28th, 2009 at 12:08 am
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?
September 28th, 2009 at 12:20 am
and what is i have another column "NAME" in between columns and ITEM and VALUE
September 28th, 2009 at 8:40 pm
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/