How To Search Multiple Items In Excel Filter

If the information you want to filter requires circuitous criteria (such equally Type = "Produce" OR Salesperson = "Davolio"), y'all can use the Advanced Filter dialog box.

To open the Avant-garde Filter dialog box, click Data > Advanced.

The Sort & Filter group on the Data tab

Advanced Filter	Example
Overview of advanced filter criteria
Multiple criteria, one column, any criteria truthful	Salesperson = "Davolio" OR Salesperson = "Buchanan"
Multiple criteria, multiple columns, all criteria true	Type = "Produce" AND Sales > yard
Multiple criteria, multiple columns, any criteria true	Blazon = "Produce" OR Salesperson = "Buchanan"
Multiple sets of criteria, one column in all sets	(Sales > 6000 AND Sales < 6500 ) OR (Sales < 500)
Multiple sets of criteria, multiple columns in each fix	(Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500)
Wildcard criteria	Salesperson = a name with 'u' every bit the second letter

Overview of avant-garde filter criteria

The Advanced command works differently from the Filter control in several important means.

Information technology displays the Advanced Filter dialog box instead of the AutoFilter menu.
You lot type the advanced criteria in a divide criteria range on the worksheet and above the range of cells or table that y'all want to filter. Microsoft Function Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the avant-garde criteria.

Sample information

The post-obit sample data is used for all procedures in this article.

The data includes four blank rows above the listing range that will be used as a criteria range (A1:C4) and a list range (A6:C10). The criteria range has column labels and includes at to the lowest degree one blank row between the criteria values and the list range.

To work with this data, select it in the following table, copy it, and then paste it in cell A1 of a new Excel worksheet.

Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

Comparison operators

You can compare two values by using the following operators. When ii values are compared by using these operators, the result is a logical value—either True or Fake.

Comparison operator	Meaning	Example
= (equal sign)	Equal to	A1=B1
> (greater than sign)	Greater than	A1>B1
< (less than sign)	Less than	A1<B1
>= (greater than or equal to sign)	Greater than or equal to	A1>=B1
<= (less than or equal to sign)	Less than or equal to	A1<=B1
<> (not equal to sign)	Not equal to	A1<>B1

Using the equal sign to type text or a value

Because the equal sign (=) is used to signal a formula when you blazon text or a value in a jail cell, Excel evaluates what y'all type; notwithstanding, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria equally a string expression in the appropriate cell in the criteria range:

=''= entry ''

Where entryis the text or value you want to detect. For example:

What you type in the cell	What Excel evaluates and displays
="=Davolio"	=Davolio
="=3000"	=3000

Considering case-sensitivity

When filtering text information, Excel doesn't distinguish between uppercase and lowercase characters. All the same, you can use a formula to perform a case-sensitive search. For an example, see the department Wildcard criteria.

Using pre-defined names

Y'all can name a range Criteria, and the reference for the range will appear automatically in the Criteria range box. You lot can too define the name Database for the list range to exist filtered and define the proper name Extract for the area where you want to paste the rows, and these ranges will appear automatically in the List range and Re-create to boxes, respectively.

Creating criteria by using a formula

Yous can utilise a calculated value that is the issue of a formula as your criterion. Remember the following of import points:

The formula must evaluate to TRUE or FALSE.
Because you are using a formula, enter the formula as you normally would, and practise not type the expression in the following way:

=''= entry ''
Exercise non use a cavalcade label for criteria labels; either go along the criteria labels blank or use a label that is non a column characterization in the listing range (in the examples that follow, Calculated Boilerplate and Exact Match).

If you employ a cavalcade label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #Proper name? or #VALUE! in the cell that contains the benchmark. Yous can ignore this error because it does non affect how the listing range is filtered.
The formula that you lot use for criteria must utilize a relative reference to refer to the corresponding jail cell in the first row of data.
All other references in the formula must be accented references.

Multiple criteria, one column, whatever criteria true

Boolean logic: (Salesperson = "Davolio" OR Salesperson = "Buchanan")

Insert at least three bare rows above the list range that tin exist used every bit a criteria range. The criteria range must have column labels. Brand sure that at that place is at least one blank row betwixt the criteria values and the list range.

To observe rows that meet multiple criteria for one column, blazon the criteria directly below each other in separate rows of the criteria range. Using the example, enter:

Type	Salesperson	Sales
	="=Davolio"
	="=Buchanan"

Click a cell in the list range. Using the example, click any cell in the range A6:C10.
On the Information tab, in the Sort & Filter group, click Advanced.
Practice 1 of the following:
- To filter the list range by hiding rows that don't match your criteria, click Filter the listing, in-place.
- To filter the list range past copying rows that match your criteria to some other area of the worksheet, click Copy to some other location, click in the Copy to box, and and then click the upper-left corner of the area where you lot want to paste the rows.
  
  Tip When you copy filtered rows to another location, you lot tin can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When yous filter, enter a reference to the copied column labels in the Re-create to box. The copied rows will so include only the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$three.

To move the Advanced Filter dialog box out of the way temporarily while y'all select the criteria range, click Collapse Dialog .

Using the example, the filtered consequence for the listing range is:

Type	Salesperson	Sales
Meat	Davolio	$450
produce	Buchanan	$6,328
Produce	Davolio	$six,544

Multiple criteria, multiple columns, all criteria true

Boolean logic: (Type = "Produce" AND Sales > yard)

Insert at least three blank rows to a higher place the list range that can be used as a criteria range. The criteria range must have cavalcade labels. Brand sure that there is at least one blank row between the criteria values and the list range.

To observe rows that meet multiple criteria in multiple columns, type all the criteria in the same row of the criteria range. Using the example, enter:

Type	Salesperson	Sales
="=Produce"		>1000

Click a jail cell in the list range. Using the example, click whatever jail cell in the range A6:C10.
On the Information tab, in the Sort & Filter group, click Advanced.
Exercise one of the following:
- To filter the list range by hiding rows that don't match your criteria, click Filter the list, in-place.
- To filter the listing range by copying rows that friction match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and and then click the upper-left corner of the expanse where you desire to paste the rows.
  
  Tip When y'all re-create filtered rows to another location, you can specify which columns to include in the copy operation. Earlier filtering, copy the column labels for the columns that you desire to the beginning row of the area where y'all plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will and so include only the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the case, enter $A$1:$C$two.

To move the Avant-garde Filter dialog box out of the manner temporarily while y'all select the criteria range, click Collapse Dialog .

Using the example, the filtered result for the list range is:

Blazon	Salesperson	Sales
produce	Buchanan	$6,328
Produce	Davolio	$6,544

Multiple criteria, multiple columns, any criteria truthful

Boolean logic: (Type = "Produce" OR Salesperson = "Buchanan")

Insert at least three blank rows above the list range that tin can be used every bit a criteria range. The criteria range must have cavalcade labels. Brand sure that there is at least i blank row between the criteria values and the listing range.

To find rows that come across multiple criteria in multiple columns where any criteria can exist truthful, type the criteria in the different columns and rows of the criteria range. Using the example, enter:

Type	Salesperson	Sales
="=Produce"
	="=Buchanan"

Click a jail cell in the list range. Using the case, click any jail cell in the listing range A6:C10.
On the Information tab, in the Sort & Filter group, click Advanced.
Exercise one of the following:
- To filter the listing range by hiding rows that don't lucifer your criteria, click Filter the list, in-place.
- To filter the list range past copying rows that match your criteria to another surface area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the surface area where y'all want to paste the rows.
Tip:When yous copy filtered rows to another location, you tin specify which columns to include in the copy operation. Before filtering, re-create the column labels for the columns that you want to the start row of the area where you plan to paste the filtered rows. When yous filter, enter a reference to the copied cavalcade labels in the Copy to box. The copied rows will then include only the columns for which yous copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$one:$B$3.

To movement the Advanced Filter dialog box out of the way temporarily while yous select the criteria range, click Plummet Dialog .

Using the case, the filtered event for the list range is:

Type	Salesperson	Sales
produce	Buchanan	$vi,328
Produce	Davolio	$6,544

Multiple sets of criteria, one column in all sets

Boolean logic: ( (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) )

Insert at least three bare rows in a higher place the list range that can be used as a criteria range. The criteria range must take column labels. Make sure that there is at least one blank row betwixt the criteria values and the list range.

To notice rows that meet multiple sets of criteria where each set includes criteria for one column, include multiple columns with the same column heading. Using the case, enter:

Type	Salesperson	Sales	Sales
		>6000	<6500
		<500

Click a prison cell in the listing range. Using the example, click whatever jail cell in the listing range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Do one of the following:
- To filter the listing range by hiding rows that don't match your criteria, click Filter the listing, in-place.
- To filter the list range by copying rows that match your criteria to some other area of the worksheet, click Copy to another location, click in the Re-create to box, and then click the upper-left corner of the area where you lot want to paste the rows.
  
  Tip:When you re-create filtered rows to some other location, you can specify which columns to include in the re-create operation. Before filtering, copy the column labels for the columns that yous want to the first row of the area where you plan to paste the filtered rows. When you lot filter, enter a reference to the copied column labels in the Re-create to box. The copied rows will and so include only the columns for which yous copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the case, enter $A$1:$D$3.

To move the Advanced Filter dialog box out of the style temporarily while you select the criteria range, click Plummet Dialog .

Using the instance, the filtered result for the list range is:

Type	Salesperson	Sales
Meat	Davolio	$450
produce	Buchanan	$half-dozen,328

Multiple sets of criteria, multiple columns in each set up

Boolean logic: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )

Insert at least three bare rows to a higher place the list range that can be used equally a criteria range. The criteria range must have cavalcade labels. Make certain that there is at least one blank row betwixt the criteria values and the list range.

To detect rows that run across multiple sets of criteria, where each set includes criteria for multiple columns, blazon each prepare of criteria in separate columns and rows. Using the example, enter:

Type	Salesperson	Sales
	="=Davolio"	>3000
	="=Buchanan"	>1500

Click a jail cell in the list range. Using the example, click any cell in the list range A6:C10.
On the Data tab, in the Sort & Filter group, click Avant-garde.
Do one of the following:
- To filter the list range by hiding rows that don't friction match your criteria, click Filter the list, in-place.
- To filter the list range by copying rows that match your criteria to another surface area of the worksheet, click Re-create to another location, click in the Copy to box, and so click the upper-left corner of the area where you want to paste the rows.
  
  Tip When yous copy filtered rows to another location, you tin specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that y'all want to the showtime row of the expanse where yous programme to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will so include just the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the case, enter $A$1:$C$three.To move the Advanced Filter dialog box out of the manner temporarily while you select the criteria range, click Collapse Dialog .

Using the example, the filtered result for the list range would be:

Type	Salesperson	Sales
produce	Buchanan	$vi,328
Produce	Davolio	$six,544

Wildcard criteria

Boolean logic: Salesperson = a name with 'u' as the second letter

To discover text values that share some characters only non others, exercise ane or more than of the following:

Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you blazon the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis."

Use a wildcard character.

Use	To find
? (question mark)	Any single character For example, sm?thursday finds "smith" and "smyth"
* (asterisk)	Any number of characters For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~	A question marking, asterisk, or tilde For example, fy91~? finds "fy91?"

Insert at least three blank rows in a higher place the list range that can exist used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the listing range.

In the rows below the cavalcade labels, type the criteria that you want to match. Using the example, enter:

Type	Salesperson	Sales
="=Me*"
	="=?u*"

Click a jail cell in the list range. Using the example, click whatever prison cell in the list range A6:C10.
On the Information tab, in the Sort & Filter group, click Advanced.
Do 1 of the following:
- To filter the listing range by hiding rows that don't match your criteria, click Filter the list, in-place
- To filter the list range past copying rows that match your criteria to another area of the worksheet, click Re-create to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.
  
  Tip:When yous copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that y'all want to the first row of the area where you lot plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows volition then include just the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the case, enter $A$1:$B$3.

To motility the Advanced Filter dialog box out of the manner temporarily while you select the criteria range, click Plummet Dialog .

Using the example, the filtered result for the list range is:

Type	Salesperson	Sales
Beverages	Suyama	$5,122
Meat	Davolio	$450
produce	Buchanan	$6,328

Demand more than help?

Y'all tin can always ask an expert in the Excel Tech Community or get support in the Answers community.