How To Search Multiple Items in Excel Filter (2 Ways)

We have a simple table having a few random Persons from different Countries and their respective Favorite Sports and Favorite Player. We will filter the dataset based on different items.

How to Search Multiple Items with Filter in Excel: 2 Methods

Method 1 – Using the Basic Filter Option

Case 1.1 – Using the Filter Directly

You will find the Filter option in the Sort & Filter section from the Data tab.

Steps:

Select the range of data you want to use the filter on and click Filter.

You will find the filter icon at the bottom corner of the column’s headers.

Click on any of the filter icons for the column you want to filter. For example, we will filter based on the Country.

All the country names will be visible. Since our agenda is to use several items for filtering, we will select a few countries from there.
Select a country. Here we have selected Australia.

Repeat for other countries of your choice.

We have selected Canada and the US.
Click on OK.
The table will hide cells that don’t contain the listed countries in the appropriate cell.

Let’s also filter through the Favorite Sports columns. Click the filter icon for that column.

Select any of the options from there. We chose Football and Tennis.

Here we will find the filtered data.

Case 1.2 – Using a Helper Column for Filtering

Steps:

List the items to find within the dataset.

We have listed three countries separately from our dataset.
We created a helper column.

Enter the following formula into the first cell of the column:

=COUNTIF($H$5:$H$7,C5)

H5:H7 is the range reference for our searching counties, and C5 is the first cell from Country.

We have found the instance number of the country (US) in the search country list.

Use AutoFill to fill in the Helper (Helping) Column. The formula puts 1 if it finds any of the listed countries in the appropriate cell in the row.

Use the Filter option on the Helper (Helping) Column and select 1 from there.

We have found the data from our desired countries.

Method 2 – Using the Advanced Filter Option to Search Multiple Items

Case 2.1 – Multiple Values for Single Column

We have listed a couple of sports in a separate table to filter by.

Steps:

Make sure the search column has the same header name as the original column.
Click on Advanced Filter from the Sort & Filter option in the Data tab.

An Advanced Filter dialog box will pop up in front of you.

Select the List range and the Criteria range.

We put our dataset into the List range and the search column into the Criteria range.
Click OK.

Case 2.2 – Multiple Values for Multiple Columns

We are going to filter from the Country and the Favorite Sports columns with a few values each.

Steps:

Open the Advanced Filter and set the ranges to the respective fields.
We have put the dataset into the List range and the search columns into the Criteria range.
Click OK.