Extract all rows from a range that meet criteria in one column in excel
Question: 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
The question can be found in this blog post: Extract all rows that contain a value between this and that
Answer:
This formula can be used with whatever size and shape of range. To search the first column, type 1 in cell C15.
Array formula in A19:D19
copied down as far as necessary.
Named ranges
tbl (A2:A19)
What is named ranges?
How to customize the formula to your excel spreadsheet
Change the named range. If your list starts at F3, change A19:$A$19 in the above array formula to $F$2:F2.
$C$13 is a cell reference to a start value criterion.
$C$14 is a cell reference to an end value criterion.
$C$15 is the column number where the above criteria must be met.
Download excel sample file for this tutorial.
Extract all records that meet criteria in a column.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
ROWS(array) returns the number of rows in a reference or an array
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
Related blog posts
- Sum the cells in a range that meet multiple criteria in excel
- Extract numbers and text from a range using array formula in excel
- Extract all rows that contain a value between this and that
- How to create a unique distinct list where other columns meet two criteria
- Match two criteria and return multiple rows in excel
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
September 29th, 2009 at 1:08 am
No need for array formulas. Extracting rows that match certain criteria would probably be better done by an advanced filter.
September 29th, 2009 at 8:41 pm
Yes, probably.
There are a lot of steps to create an advanced filter. Doing this multiple times might be timeconsuming? Creating an array formula can also be timeconsuming but when in place only two cell values (C14 and C15) need to be changed to create a new search.
The above array formula is quite small and can be used with a range of any size and shape.
I might be wrong, what do you think?
February 25th, 2010 at 2:50 am
Hi - I have tried to follow your solution above however only the first record that matches the criteria is displayed over and over again - can you think of what i might be doing wrong?
February 25th, 2010 at 2:55 am
Hi - bit more clarity - when i downloaded your example, I highlighted the search results section and pressed Ctril + Shift + Enter and the same error came up?
March 3rd, 2010 at 10:10 am
John,
1. Select A19:D19
Copy the array formula to A19:D19 into the formula field + CTRL + SHIFT + Enter , in the example above.
2. Select A19:D19
3. Copy (Ctrl + C)
4. Select B19:D25
5. Paste (Ctrl + V)
Copying cells changes relative references in formulas.
Here is an excellent explanation of absolute and relative references:
http://www.cpearson.com/excel/relative.aspx