To make it easy to filter for several different items, you 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.
Video: Filter Exact Match List Items
Watch this video to see how to set up an Advanced Filter, and filter for exact matches in the item list.
To get the Microsoft Excel file, so you follow along with the video, go to the Excel Advanced Filter Criteria page on my Contextures site. In the download section, look for the Match Items in List sample workbook
NOTE: There are written steps below the video, for another Filter to Match List Items example.
Two Options for Filtering
Here’s the sample data, and the list of items that we want to filter. The table in column F is named tblFind, and cells F2:F3 are named FindList.
We’ll look at two options for filtering the list:
- Filter rows that have an exact Product match for items in the list
- 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 column header in the data table, or leave the criteria heading blank.
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, and show the results in place, follow these steps:
- Select a cell in the data table
- On the Data tab of the Ribbon, in the Sort & Filter group, click Advanced, to open the Advanced Filter dialog box
- For Action, select Filter the list, in-place
- For List range, select the data table
- For Criteria range, select C1:C2 – the criteria heading and formula cells
- Click OK, to see the results
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.
Partial Match: Contain Item in List
For the second filter, we want to find rows with a product that contains one of the list items, anywhere in the Product name 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.
- Tip: See more SUMPRODUCT examples on my Contextures site.
Run the Filter
Next, run the Advanced Filter with the same settings as in the first example, and the 6 rows that contain “Milk” or “Cookies” (upper or lower case does not matter) in the product cell are visible.
Download the Sample File
To get the Excel file for the Exact Match video, go to the Excel Advanced Filter Criteria page on my Contextures site. In the download section, look for the Match Items in List sample workbook
The zipped file is in Excel xlsx format, and does not contain any macros
Related Links
Advanced Filter Criteria Slicers
____________
it looks like a lot of steps were skipped creating the list and tables so it is very difficult to follow
Thank you very much!!!
i have been trying to filter a list of values in a sea of data and this helped and saved my time a lot.
I tried to google on how to filter specific values and came upon this article while everything else failed.
I wish you a happy Christmas and Happy New Year.
how do i find list of item in a range and put logical value yes or no
for e.g my list item in cell a1:a3 like laptop, mobile, washing machine,
my range b1:b1000 contain data like laptop lenova, samsung mobile contain in cell b1:b1000
i want logical value in c1:c1000 yes or no
the column i need to filter on contains text and numeric data. the text data includes the numeric data with a prefix.
column A
12345
12346
pre-12345
pre-12346
…
my FindList only contains the numeric data (12345, 12346, etc.). if i use traditional filters, filtering on 12345 returns both numeric (12345) and text rows (pre-12345) that match. using the SUMPRODUCT(COUNTIF(C5,”*”& FindList &”*”)) method only returns the text rows.
any guidance on getting past this blocker is appreciated. thanks,
alex
Alex, you can try this formula instead:
=SUMPRODUCT(–ISNUMBER(SEARCH(FindList,C5)))
Based on my “find numbers within numbers” examples here:
https://www.contextures.com/xlFunctions04.html#numinnum
Hi Debra, thanks for this – it’s really useful.
I think you have an error in one of the steps under “Run the Advanced Filter”. It says:
For Criteria range, select C1:C1, but it should say:
For Criteria range, select C1:C2
Cheers
Thanks, Adi, and it’s fixed now!
Small tip on the search list.
The search list cells can’t be blank, in the example cells F2 and F3 must contain a value, because if they are blank your countif is for ** which will now filter nothing…
My find list could contain one value (F2) or 5 values (F2:F6 my defined FindList).
So have all of the FindList cells =”zzzzzzzzzzzz” or some value that will never be in your filter column.
This way the you (or the user) on replaces the zzzzzzzz with a value you are searching for.
I probably didn’t explain this very well, but hopefully you get my meaning 😉