How Many Rows Must The Criteria Range Of An Advanced Filter Have?

Author: Oscar Cronquist Article last updated on September 11, 2021

I will in this article demonstrate several techniques that extract or filter records based on ii conditions practical to a single cavalcade in your dataset. For case, if you use the array formula then the result will refresh instantly when you enter new start and cease values.

The remaining congenital-in techniques need a petty more than manual piece of work in guild to apply new conditions, however, they are fast. The downside with the array formula is that it may get slow if you are working with huge amounts of data.

I accept also written an article in case yous need to observe records that lucifer 1 condition in one column and another condition in another column. The following commodity shows y'all how to build a formula that uses an capricious number of weather: Excerpt records where all criteria match if non empty

This article Excerpt records betwixt 2 dates is very similar to the electric current ane yous are reading right at present, Excel dates are actually numbers formatted as dates in Excel. If you desire to search for a text cord within a given date range and then read this article: Filter records based on a date range and a text string

I must recommend this article if you want to practice a wildcard search beyond all columns in a data set, it also returns all matching records. If you desire to extract records based on criteria and not a numerical range then read this part of this commodity.

What is on this folio?

Extract all rows from a range based on range criteria (Array formula)
1. Video
2. How to enter an assortment formula
3. Explaining array formula
Extract all rows from a range based on range criteria - Excel 365
1. Explaining formula
Extract all rows from a range based on multiple conditions (Array formula)
1. Explaining array formula
Extract all rows from a range based on multiple conditions - Excel 365
1. Explaining formula
Excerpt all rows from a range based on range critera
[Excel defined Table]
Extract all rows from a range based on range critera
[Filter]
Extract all rows from a range based on range criteria
[Advanced Filter]
Get Excel file

i. Extract all rows from a range based on range criteria
[Assortment formula]

The moving-picture show above shows you a dataset in cell range B3:E12, the search parameters are in D14:D16. The search results are in B20:E22.

Update 20 Sep 2017, a smaller formula in jail cell A20.

Assortment formula in prison cell A20:

=INDEX($B$three:$E$12, Pocket-sized(IF((Alphabetize($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$three:$East$12, , $D$sixteen)> =$D$14), Friction match(ROW($B$3:$Eastward$12), ROW($B$3:$Eastward$12)), ""), ROWS(B20:$B$twenty)), COLUMNS($A$1:A1))

1.1 Video

See this video to learn more well-nigh the formula:

Back to pinnacle

1.2 How to enter this array formula

Select cell A20
Paste above formula to cell or formula bar
Press and hold CTRL + SHIFT simultaneously
Press Enter once
Release all keys

The formula bar now shows the formula with a beginning and ending curly bracket, that is if y'all did the above steps correctly. Like this:

{=array_formula}

Don't enter these characters yourself, they appear automatically.

Now copy prison cell A20 and paste to cell range A20:E22.

Dorsum to pinnacle

i.3 Explaining array formula in jail cell A20

You tin follow along if you select jail cell A19, go to tab "Formulas" on the ribbon and press with left mouse push on the "Evaluate Formula" push button.

Step 1 - Filter a specific cavalcade in cell range B3:E12

The INDEX function is by and large used for getting a single value from a given cell range, withal, it can also return an entire column or row from a cell range.

This is exactly what I am doing here, the cavalcade number specified in prison cell D16 determines which cavalcade to extract.

Alphabetize($B$3:$Eastward$12, , $D$16, one)

becomes

Index($B$iii:$E$12, , iii, 1)

and returns C3:C12.

How to use the Alphabetize role

Pace 2 - Check which values are smaller or equal to status

The smaller than and equal sign are logical operators that permit you compare value to value, in this case, if a number is smaller than or equal to another number.

The output is a boolean value, True och False. Their positions in the array correspond to the positions in the cell range.

Index($B$three:$E$12, , $D$sixteen, 1)< =$D$15

becomes

C3:C12< =$D$fifteen

becomes

{two; 6; 4; 5; 3; nine; three; 2; 0; 1}<=vi

and returns

{TRUE; True; True; TRUE; TRUE; Simulated; TRUE; Truthful; Truthful; True}.

Step 3 - Multiply arrays - AND logic

There is a second condition we need to evaluate before we know which records are in range.

(Alphabetize($B$3:$Eastward$12, , $D$16, i)< =$D$15)*(Index($B$3:$Eastward$12, , $D$16, 1)> =$D$fourteen)

becomes

({two; 6; four; 5; 3; nine; 3; 2; 0; 1}< =$C$14)*({ii; 6; 4; 5; iii; 9; 3; 2; 0; 1}> =$C$13)

becomes

({2; 6; 4; 5; 3; ix; iii; ii; 0; 1}< =three)*({ii; vi; four; 5; 3; 9; 3; 2; 0; i}> =0)

becomes

{TRUE; Fake; FALSE; FALSE; Truthful; Simulated; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; Truthful; True; True; TRUE; TRUE; TRUE; TRUE; Truthful}

Both atmospheric condition must be met, the asterisk lets us multiple the arrays pregnant AND logic.

TRUE * TRUE equals FALSE, all other combinations return Imitation. TRUE * FALSE equals FALSE and so on.

{TRUE; Fake; Simulated; Simulated; TRUE; FALSE; Truthful; TRUE; TRUE; TRUE} * {True; Truthful; Truthful; True; True; TRUE; Truthful; True; True; Truthful}

returns

{1; 0; 0; 0; i; 0; i; 1; 1; ane}.

Boolean values have numerical equivalents, TRUE = 1 and Faux equals 0 (nil). They are converted when you perform an arithmetic operation in a formula.

Footstep 4 - Create number sequence

The ROW role calculates the row number of a cell reference.

ROW(reference)

ROW($B$3:$E$12)

returns

{3; 4; 5; six; 7; viii; 9; x; eleven; 12}.

Step 5 - Create a number sequence from i to n

The MATCH office returns the relative position of an item in an array or cell reference that matches a specified value in a specific lodge.

MATCH(ROW($B$iii:$Due east$12), ROW($B$3:$E$12))

becomes

MATCH({3; 4; 5; 6; 7; 8; ix; 10; eleven; 12}, {3; iv; v; 6; 7; 8; 9; x; 11; 12})

and returns

{ane; 2; 3; 4; v; half-dozen; 7; 8; 9; 10}.

Step 4 - Render corresponding row number

The IF function returns 1 value if the logical test is TRUE and another value if the logical test is Simulated.

IF(logical_test, [value_if_true], [value_if_false])

IF((INDEX($B$3:$E$12, , $D$xvi)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)> =$D$14), Lucifer(ROW($B$3:$Eastward$12), ROW($B$3:$E$12)), "")

becomes

IF({1; 0; 0; 0; ane; 0; 1; 1; one; ane}, Match(ROW($B$3:$E$12), ROW($B$iii:$E$12)), "")

becomes

IF({ane; 0; 0; 0; 1; 0; 1; 1; 1; one}, {1; 2; iii; 4; 5; 6; 7; 8; 9; x}, "")

and returns

{1; ""; ""; ""; five; ""; 7; 8; 9; x}.

Step 5 - Extract 1000-th smallest row number

The Pocket-sized function returns the k-th smallest value from a group of numbers.

Small-scale(array,yard)

Small-scale(IF((Alphabetize($B$3:$E$12, , $D$sixteen)< =$D$15)*(INDEX($B$3:$E$12, , $D$xvi)> =$D$14), MATCH(ROW($B$iii:$Due east$12), ROW($B$3:$Eastward$12)), ""), ROWS(B20:$B$twenty))

becomes

Small-scale({one; ""; ""; ""; v; ""; seven; 8; nine; ten}, ROWS(B20:$B$twenty))

becomes

SMALL({i; ""; ""; ""; 5; ""; 7; viii; 9; 10}, 1)

and returns one.

Step half-dozen - Return the entire row record from cell range

The Index part returns a value from a cell range, you specify which value based on a row and column number.

INDEX(array,[row_num],[column_num])

Alphabetize($B$3:$Eastward$12, SMALL(IF((Alphabetize($B$3:$Eastward$12, , $D$16)< =$D$15)*(INDEX($B$3:$Eastward$12, , $D$xvi)> =$D$14), MATCH(ROW($B$three:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($A$1:A1))

becomes

INDEX($B$3:$E$12, 1, , 1)

and returns {2, "Ken Smith", vi, "North"}.

Recommended reading
Match two criteria and return multiple records
Excerpt records where all criteria lucifer if non empty
Extract all rows that contain a value between this and that
Excerpt records between two dates
Filter records based on a engagement range and a text string
Search for a text cord in a data set up
Excerpt records containing negative values
Extract records containing digits [Formula]

Back to elevation

two. Extract all rows from a range based on range criteria - Excel 365

Update 17 December 2020,the new FILTER function is now available for Excel 365 users. Formula in cell B20:

=FILTER($B$three:$E$12, (D3:D12<=D15)*(D3:D12>=D14))

It is a regular formula, still, it returns an array of values and extends automatically to cells below and to the right. Microsoft calls this a dynamic array and spilled array.

The array formula below is for earlier Excel versions, information technology searches for values that meet a range criterion (cell D14 and D15), the formula lets you change the column to search in with jail cell D16.

This formula can be used with whatever dataset size and shape. To search the first cavalcade, blazon 1 in prison cell D16.

Back to elevation

2.1 Explaining array formula

Step one - First status

The less than character and the equal sign are both logical operators meaning they are able to compare value to value, the output is a boolean value.

In this case,

D3:D12<=D15

Step two - 2d status

D3:D12>=D14

Pace 3 - Multiply arrays - AND logic

(D3:D12<=D15)*(D3:D12>=D14)

Stride 4 - Filter values

FILTER($B$3:$E$12, (D3:D12<=D15)*(D3:D12>=D14))

Back to tiptop

3. Excerpt all rows from a range that come across criteria in one column [Array formula]

The array formula in cell B20 extracts records where column E equals either "South" or "East".

The following array formula in prison cell B20 is for earlier Excel versions than Excel 365:

=Alphabetize($B$iii:$E$12, SMALL(IF(COUNTIF($E$fifteen:$Eastward$16,$Eastward$3:$E$12), Friction match(ROW($B$3:$E$12), ROW($B$iii:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($B$two:B2))

To enter an assortment formula, blazon the formula in a cell then press and hold CTRL + SHIFT simultaneously, at present press Enter once. Release all keys.

The formula bar at present shows the formula with a offset and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

3.1 Explaining formula in cell B20

Footstep 1 - Filter a specific column in cell range $A$ii:$D$xi

The COUNTIF function allows you to identify cells in range $E$3:$Due east$12 that equals $E$15:$E$16.

COUNTIF($E$15:$E$16,$East$three:$E$12)

becomes

COUNTIF({"Due south"; "East"},{"North"; "N"; "South"; "West"; "South"; "E"; "West"; "West"; "South"; "East"})

and returns

{0;0;1;0;1;one;0;0;1;1}.

Step two - Render corresponding row number

The IF office has iii arguments, the offset one must exist a logical expression. If the expression evaluates to TRUE so one thing happens (statement two) and if FALSE some other thing happens (argument 3).

The logical expression was calculated in step 1 , TRUE equals 1 and Faux equals 0 (nil).

IF(COUNTIF($E$xv:$E$16,$E$3:$E$12), Match(ROW($B$3:$E$12), ROW($B$iii:$East$12)), "")

becomes

IF({0; 0; 1; 0; 1; ane; 0; 0; 1; 1}, MATCH(ROW($B$3:$E$12), ROW($B$3:$Eastward$12)), "")

becomes

IF({0; 0; one; 0; one; one; 0; 0; 1; ane}, {ane; 2; 3; iv; 5; 6; 7; 8; 9; 10}, "")

and returns

{""; ""; iii; ""; 5; vi; ""; ""; 9; 10}.

Step three - Observe k-thursday smallest row number

SMALL(IF(COUNTIF($E$15:$E$16,$E$3:$E$12), Friction match(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20))

becomes

SMALL({""; ""; 3; ""; five; 6; ""; ""; 9; 10}, ROWS(B20:$B$twenty))

becomes

Minor({""; ""; 3; ""; 5; 6; ""; ""; 9; ten}, one)

and returns 3.

Step four - Render value based on row and cavalcade number

The INDEX function returns a value based on a cell reference and cavalcade/row numbers.

INDEX($B$3:$E$12, Pocket-sized(IF(COUNTIF($Eastward$15:$Eastward$xvi,$E$iii:$East$12), MATCH(ROW($B$3:$Due east$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$xx)), COLUMNS($B$2:B2))

becomes

INDEX($B$three:$E$12, 3, COLUMNS($B$ii:B2))

becomes

INDEX($B$3:$E$12, 3, 1)

and returns 3 in cell B20.

Back to superlative

4. Extract all rows from a range based on multiple atmospheric condition - Excel 365

Update 17 December 2020,the new FILTER office is at present available for Excel 365 users. Formula in jail cell B20:

=FILTER($B$iii:$E$12, COUNTIF(E15:E16, E3:E12))

It is a regular formula, nevertheless, it returns an assortment of values. Read here how it works: Filter values based on criteria

The formula extends automatically to cells below and to the correct. Microsoft calls this a dynamic assortment and spilled array.

Back to peak

4.1 Explaining array formula

Step 1 -

COUNTIF(E15:E16, E3:E12)

Footstep ii -

FILTER($B$iii:$Eastward$12, COUNTIF(E15:E16, E3:E12))

Dorsum to top

5. Extract all rows from a range that meet criteria in i column [Excel defined Table]

The image in a higher place shows a dataset converted to an Excel defined Table, a number filter has been applied to the tertiary column in the table.

Here are the instructions to create an Excel Tabular array and filter values in cavalcade three.

Select a cell in the dataset.
Press CTRL + T
Printing with left mouse button on bank check box "My table has headers".
Press with left mouse push on OK button.

The prototype above shows the Excel defined Table, here is how to filter D betwixt iv and six:

Press with left mouse button on black pointer next to header.
Press with left mouse push on "Number Filters".
Printing with left mouse push button on "Between...".
Type 4 and 6.
Printing with left mouse button on OK push.

Dorsum to top

6. Excerpt all rows from a range that meet criteria in one column [Filter]

The epitome in a higher place shows filtered records based on 2 atmospheric condition, values in cavalcade D are larger or equal to 4 or smaller or equal to vi.

Here is how to apply Filter arrows to a dataset.

Select any jail cell within the dataset range.
Go to tab "Data" on the ribbon.
Press with left mouse push on "Filter button".

Black arrows announced side by side to each header.

Lets filter records based on conditions applied to cavalcade D.

Press with left mouse button on black arrow next to header in Column D, see image below.
Press with left mouse button on "Number Filters".
Printing with left mouse push button on "Between".
Blazon 4 and 6 in the dialog box shown below.
Press with left mouse button on OK push.

Back to height

7. Extract all rows from a range that encounter criteria in one cavalcade
[Advanced Filter]

The image above shows a filtered dataset in prison cell range B5:E15 using Advanced Filter which is a powerful characteristic in Excel.

Hither is how to apply a filter:

Create headers for the column you lot desire to filter, preferably in a higher place or beneath your data set up.
Your filters will possibly disappear if placed next to the data set up because rows may become subconscious when the filter is applied.
Select the entire dataset including headers.
Become to tab "Information" on the ribbon.
Press with left mouse button on the "Advanced" button.
A dialog box appears.
Select the criteria range C2:D3, shown ithe due north in a higher place paradigm.
Press with left mouse button on OK push button.

Back to summit

viii. Excel file