Excel Filter to Match List of Items

To make it easy to filter for several different items, you can create a list of those items on a worksheet. Then, filter your data based on that list, so you don’t have to check all the items manually each time.

Two Options for Filtering

Here’s the sample data, and the list of items that we want to filter — Milk and Cookies. The table in column F is named tblFind, and cells F2:F3 are named FindList.

advancedfiltercriteria13

We’ll look at two options for filtering the list:

  1. Filter rows that have an exact Product match for items in the list
  2. Filter rows that contain an item in the list, anywhere in the Product field

Advanced Filter

Both options will use an Advanced Filter, so a Criteria range is added to the worksheet.

We’ll be using a formula in the criteria cell, so type a heading that is different from any heading in the data table, or leave the criteria heading blank.

advancedfiltercriteria14

Exact Match For Items in List

For the first filter, we want to find rows with a product that is an exact match for one of the list items.

  • In cell C2, enter the following formula. It refers to the named range in the list of items, and the first Product cell in the data table.
    =COUNTIF(FindList,C5)

The formula uses the COUNTIF function to check each record, and test for the list items. Rows with an exact match will be returned in the filter.

Run the Advanced Filter

To run the Advanced Filter:

  • Select a cell in the data table
  • On the Data tab of the Ribbon, in the Sort & Filter group, click Advanced
  • For Action, select Filter the list, in-place
  • For List range, select the data table
  • For Criteria range, select C1:C1 – the criteria heading and formula cells
  • Click OK, to see the results

Excel Filter to Match List of Items

The 4 rows that have a product that is exactly “Milk” or “Cookies” (case does not matter), are visible, and all other rows are hidden.

advancedfiltercriteria16

Contain an Item in the List

For the second filter, we want to find rows with a product that contains one of the list items, anywhere in the Product cell.

  • In cell C2, enter the following formula. It refers to the named range in the list of items, and the first Product cell in the data table.
    =SUMPRODUCT(COUNTIF(C5,”*”& FindList &”*”))>0

The COUNTIF function checks each Product cell, and tests for the list items. The * wildcards are used before and after the list item, so the text can be found anywhere in the Product cell. The SUMPRODUCT function sums the number, and if it’s greater than zero, the result is TRUE.

Run the Advanced Filter with the same settings as in the first example, and the 6 rows that contain “Milk” or “Cookies” (case does not matter) in the product cell are visible.

advancedfiltercriteria17

Advanced Filter Video

Watch this video to see how to set up the Advanced Filter, and filter for exact matches in the item list.

Download the Sample File

To download the workbook with sample data and Excel advanced filter criteria, go to the Advanced Filter Criteria page on my Contextures website. Scroll down to the Download section, and click the download link. The zipped file is in xlsx format, and does not contain macros.

____________

Save

Save

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *