Filters > Criteria

Filter Excel Data Based on Criteria List

How to filter Excel data based on criteria in worksheet list. Use Advanced Filter for exact or partial match.

Filter Data Based on Criteria List

For most filters in Excel, you use 1 or 2 criteria, and show the rows that contain those values. However, if you need to use more criteria, the examples below show how to set up a criteria list on a worksheet. After that, you can filter your data based on that criteria list.

There are 2 types of filter examples below:

1. Filter rows that have an exact match for items in the criteria list
2. Filter rows that have an exact or partial match for items in the criteria list

There are 3 methods for filtering the data:

1. Advanced Filter (all versions of Excel)
2. AutoFilter (exact match only, all versions of Excel)
3. FILTER function (exact match only, Excel 365 )

Advanced Filter - Exact Match

This video shows how to set up an Advanced Filter, with a list of criteria items on the worksheet. When the filter is applied, all rows with an exact match in the criteria list, and over the minimum total, are shown in the results. Written steps are below the video.

• Tip: To follow along with the video, get the Excel workbook used in the video, Filter to Match Items in List. The zipped workbook is in xlsx format, and does not contain any macros

Advanced Filter Criteria

Two criteria are used in this advanced filter example:

1. Total (column D) must be greater than 1000
2. Product name (column C) must have an exact match in criteria list (column I)

• NOTE: To filter the results to a different sheet, start the filter from the destination sheet. See details on the Advanced Filter page.

To set up the Advanced Filter:

1. In Column I, enter the list of items that you want to match -- Bread and Cookies, in this example.
2. Enter the following values/formulas in the Criteria range:
   • F1: Criteria Heading -- "Total"
   • F2: Criteria:   >1000
   • G1: Criteria Heading -- Leave blank
   • G2: Criteria:   =COUNTIF(I:I,C2)

In each record, the value in column C is checked. If it is in the list in column I, and the total is greater than 1000, the record passes through the Excel advanced filter.

Advanced Filter - Partial Match

In this example, the worksheet setup is identical to the Advanced Filter - Exact Match, shown above. However, there is a different formula in criteria cell G2, shown below.

When the filter is applied, all rows with an exact or partial match in the criteria list, and over the minimum total, are shown in the results.

Advanced Filter Criteria

Two criteria are used in this advanced filter example:

• total must be greater than 100
• product must contain an item in the list in column I, anywhere in the product cell

To set up the Criteria List:

1. In Column I, enter the list of items that you want to match -- Bread and Cookies, in this example.
2. Format the list as a named Excel Table, (tblFind in this example)
3. Select the list of items in the table (do not select the heading cell)
4. Click in the Name box, at the left of the Formula Bar
5. Type a one-word name for the criteria list items - FindList
6. Press Enter, to complete the name
   • NOTE: If you skip this step, the name is NOT create

To set up the Criteria Range and Formula:

1. Enter the following values/formulas in the Criteria range:
   • F1: Criteria Heading -- "Total"
   • F2: Criteria:   >100
   • G1: Criteria Heading -- Leave blank
   • G2: Criteria:   =SUMPRODUCT(COUNTIF(C2,"*"& FindList &"*"))>0

How the Formula Works

Here's how the criteria formula in cell G2 works

• The COUNTIF function uses * (asterisk) wildcard characters, to represent any number of unknown characters, including zero, before and after the text in the criteria items
• The SUMPRODUCT function calculates the total from the COUNTIF results
• If the SUMPRODUCT total is greater than zero, an exact or partial match was found in the criteria list.

In each record, the formula checks the value in column C.

• If that value contains an item from the list in column I,
• AND the total is greater than 100,

the record passes through the Excel advanced filter.

AutoFilter - Exact Match

If your data is in a named Excel Table, the headings have drop down lists, AutoFilters, where you can select one or more items to filter the list.

This type of filter is an  AutoFilter, and you can select criteria options from the drop down list.

• Text Filters - Enter 1 or 2 criteria, with AND / OR as the operator
  • Type the criteria values into a dialog box
  • You cannot refer to worksheet cells for the criteria
• Item List - Click check boxes to select one or more of the items in the column

No Criteria Range Option

Unfortunately, an AutoFilter does not allow you to select a worksheet range as a criteria source, so there's no built-in way to use a worksheet criteria list.

However, I've created an AutoFilter Criteria List macro that you can download, and use it to filter your data, based on one or more criteria lists.

In the sample file, there are two criteria lists, for the Customer and Product columns in the main data table. You can add or remove items in those tables, as needed.

NOTE: Because of AutoFilter limitations, only exact matches for the criteria list items can be filtered.

Beside the main data table, there are worksheet buttons that let you apply filters, based on those criteria lists, or clear all the filters.

You can get the sample file in the Download section on this page, and the setup instructions and details are on the AutoFilter Criteria List Macro page.

FILTER Function - Exact Match

In Excel 365, or other Excel versions that support the new Spill Functions, you can use the FILTER function to filter data, based on a criteria list.

In this example, there is a sales table named tblSales, and cell G2 has a criteria value, for the minimum sales Total.

In column I, the criteria list is like the Advanced Filter Partial Match setup, shown in a section above.

• There is a named table, tblFind
• Item cells in the table are a named range, FindList

FILTER Function Formula

In cell K2, there is a FILTER function formula, to return the sales order data, based on:

• Minimum amount in cell G2
• Items in the named range, FindList

Here is the formula in cell K2:

• =FILTER(tblSales,(tblSales[Total]>=G2) * (COUNTIF(FindLIst,tblSales[Product])))

The results spill across to column N, and down as far as needed. The spill range is outlined with a blue border.

In the screen shot below, the orders for bread, milk and cookies, with a total over 500, are shown in the results.

• Note: If you change the minimum amount, or add/remove items in column I, the formula results update automatically

Get the Sample Files

Advanced Filter Exact: Get the Excel workbook used in the video, Advanced Filter Exact Match. The zipped workbook is in xlsx format, and does not contain any macros.

AutoFilter Macro: To test the AutoFilter macro, download the Table Criteria List workbook. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the macros.

FILTER Function Exact: To test the new FILTER function, download the FILTER Function from List workbook. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

Advanced Filter Criteria

AutoFilter Basics

FILTER Examples

FILTER Function Reports