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

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Dataset of Searching 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 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.

Selection of Filter Option to Search Multiple Items

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

Basic Filter Icon for Every Column

  • 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

Using Filter Icon to a Column

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.

Filtering a Single Item From Filter Option

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

Selection of Multiple Items using a Checkmark

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

Output of Filtered Data for Multiple Items

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.

Filtering Multiple Columns to Search Multiple Items

Step 3:

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

Filtering Multiple Items From Filter Option

  • Here we will find the filtered data.

Output of Filtered Item

Read More: How to Apply Multiple Filters in Excel


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.

Sample Dataset of Searching Multiple Items Using Helper Column

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

Adding New Helper Column to Search Multiple Items

  • 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:
=COUNTIF($H$5:$H$7,C5)

Where,

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

Applying the COUNTIF function to count cells with criteria

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

Result of Applied Formula

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

Utilizing Autofill Feature to Fill

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

Performing Filter Feature to Filter Items

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

Output of Filtered Multiple Items using Helper Column

Read More: How to Filter Multiple Values in One Cell in Excel


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.

Sample Dataset for Searching Multiple Items Using Advance Filter

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

Utilizing the Advanced Filter Option to Filter

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

Advanced Filter Window to Filter Data table

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

Selection of List Range and Criteria Range to Filter Multiple Items

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

Output with Multiple Choices Using Advanced Filter

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

Read More: How to Filter Multiple Columns Simultaneously in Excel


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.

Sample Dataset for Searching Multiple Items Using Advance Filter

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.

Advanced Filter Window to Filter Data table

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

Output with Multiple Choices using Advanced Filter

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

Read More: How to Filter Multiple Rows in Excel


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.


Further Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
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.

1 Comment
  1. thanks a lot

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo