How To Search Multiple Items in Excel Filter

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 the filter.

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

Dataset - How To Search Multiple Items In Excel Filter

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 the real-life scenario you may encounter many complex and large datasets.

Practice Workbook

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

Search Multiple Items in Excel Filter

1. Using Basic Filter Option

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

I. 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.

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

Filter option - How To Search Multiple Items In Excel Filter

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

Filter Icon - How To Search Multiple Items In Excel Filter

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 column.

Select items - How To Search Multiple Items In Excel Filter

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.

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

Select item 1 - How To Search Multiple Items In Excel Filter

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

Select Multiple items - How To Search Multiple Items In Excel Filter

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

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

Filter data - How To Search Multiple Items In Excel Filter

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, now we are going to filter through the Favorite Sports columns. You need to click the filter icon from that column.

Select data from different column-How To Search Multiple Items In Excel Filter

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

Select items 2 - How To Search Multiple Items In Excel Filter

Here we will find the filtered data.

Filter data - How To Search Multiple Items In Excel Filter

II. 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.

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

Helper column data - How To Search Multiple Items In Excel Filter

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

Helper column - How To Search Multiple Items In Excel Filter

We will fill this Helper (Helping) Column using a formula formed by the COUNTIF function. COUNTIF counts the cells with criteria. To know more about the function, visit this COUNTIF article.

The formula will be

=COUNTIF($H$4:$H$6,C4)

H4:H6 is the range reference for our searching counties, and C4 is the first cell from the Country column.

COUNTIF - How To Search Multiple Items In Excel Filter

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

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

AutoFill - How To Search Multiple Items In Excel Filter

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

Filter in Helper data - How To Search Multiple Items In Excel Filter

Here we have found the data from our desired countries.

Filter data - How To Search Multiple Items In Excel Filter

2. Using Advanced Filter Option

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

I. Multiple Values for Single Column

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

Advanced option - How To Search Multiple Items In Excel Filter

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 tab.

Advanced Filter - How To Search Multiple Items In Excel Filter

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

Advanced filter dialog -How To Search Multiple Items In Excel Filter

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

Data in dialog box - How To Search Multiple Items In Excel Filter

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

Filtered data using Advanced filter - How To Search Multiple Items In Excel Filter

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

II. 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.

Multiple columns in Advanced filter - How To Search Multiple Items In Excel Filter

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

Now exercise the Advanced Filter and set the ranges to the respective fields.

Set data in ranges - How To Search Multiple Items In Excel Filter

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

Filtered data from multiple columns - How To Search Multiple Items In Excel Filter

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.

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 which we might have missed here.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo