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

One of the more common tasks in Excel is filtering data. Probably in every aspect of your Excel use, you are using the filter, filtering can be done with single items as well as multiple items. Today we are going to show you how to search multiple items in an Excel filter.

First things first, let’s get to know about today’s example dataset.

We have a simple table having a few random Persons from different Countries and their respective Favorite Sports and Favorite Player. Using this dataset we will filter with different items.

Note that it is a basic dataset, in a real-life scenario you may encounter many complex and large datasets.

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

1. Using Basic Filter Option

The basic filter option can be useful to search multiple items. Let’s explore this tool.

1.1. Using Filter Directly

We can use the basic filter option directly to search multiple items. You will find this Filter option in the Sort & Filter section from the Data tab.

Step 1:

• First, select the range of data you want to use the filter, and then click the Filter.

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

• Now we need to click on any of the filter icons, for which we want to filter our data. For example, we are going with the Country

All the countries’ names will be visible. Since our agenda is to use several items for filtering, we will select a few countries from there.

Step 2:

• First of all, select a country. Here we have selected Australia.

• One item has been selected, now we need to select a couple of items more (since we are going to search for multiple items).

• Here we have selected Canada and the US. You can choose yours. Now click OK.

Only the data from these three countries have been in front of us.

We have filtered our dataset with multiple items (countries). Not only within a single column but also for multiple columns we can perform our search.

In our example, we are going to filter through the Favorite Sports columns. You need to click the filter icon from that column.

Step 3:

• Now select any of the options from there, here we are selecting Football and Tennis.

• Here we will find the filtered data.

1.2. Using a Helper Column for Filtering

In the earlier section, we have directly used the Filter option. Now we are going to see how to filter using a helper column.

Steps:

• Here we have to list the items first that we want to find within our dataset.

• We have listed three countries separately from our dataset. And introduced a helper column.

• We will fill this Helper (Helping) Column using a formula formed by the COUNTIF function. The COUNTIF function counts the cells with criteria. The formula will be:

Where,

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

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

• Exercise Excel AutoFill to fulfill the Helper (Helping) Column. Where countries matched we found 1 otherwise 0.

• Now use the Filter option on the Helper (Helping) Column and select 1 from there.

• Here we have found the data from our desired countries.

2. Using the Advanced Filter Option to Search Multiple Items

We can use the Advanced Filter option to search multiple items in Excel. Let’s explore the method.

2.1. Multiple Values for Single Column

We can search multiple values in a single column. Here we have listed a couple of sports.

Steps:

• Make sure the search column has the same name as the original column.
• Now, click the Advanced Filter from the Sort & Filter option in the Data

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

• Then you need to select the List range and the Criteria range.

• Here we have selected our dataset into the List range and the search column into the Criteria range.
• Now click OK.

Our searched sports were Football and Cricket. And we have found only these sports in our dataset.

2.2. Multiple Values for Multiple Columns

We can use multiple columns while searching values. In our example, we are going to filter from the Country and the Favorite Sports.

Here we have taken the US and India for searching within the Country column and Football and Cricket for the Favorite Sports column.

Steps:

• Now exercise the Advanced Filter and set the ranges to the respective fields.
• Here we have selected our dataset into the List range and the search columns into the Criteria range.
• Now click OK.

We have found the dataset concerning the values from our search items.

Note that when we have used multiple columns, the filtering process will assume them as individual rows.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Conclusion

That’s all for the session. We have listed a couple of ways to search multiple items in the Excel filter. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know which of the methods you are going to use. Notify us of the approaches that we might have missed here.

<< Go Back to Filter in Excel | Learn Excel