# How to Filter Data Based on Matching Columns in Excel?

Filtering data is a crucial part of data analysis in Excel, especially when dealing with large datasets. Excel provides various functions to filter data, and one such function is FILTER used in combination with SEARCH or FIND for text matching.

#### Scenario: Locating Employees Based on User-Input City

Consider an HR department that needs to quickly locate all employees based in a city specified by the user. Perhaps there’s an upcoming regional meeting or a need to assess the number of employees in a particular location for a new office setup.

There is an existing data set (as a table EMP_DATA) in Excel where all the details of the employees are stored.

In our dataset, let’s assume users will input their desired city into a named range called CitySelected.

We want to filter the dataset to extract all employees whose location contains the city name provided by the user. Along with this we need only select information from the employee table like the ID, name, email address, and the department.

Note that these are not adjacent columns in the given data.

#### The FILTER Function

The FILTER function in Excel, which has the following syntax:

Our need here is to extract the number, name, email address, and department from the EMP_DATA table (the range) and the condition for filtering (criteria) would be that the CitySelected is equal to the Location column data from the employee table.

First, the EMP_DATA table DOES NOT have all of these ranges as adjacent columns so our range to filter needs to be extracted using an INDEX function. This will be:

The syntax for the Index is:

#### What’s happening here?

We have used the INDEX function to retrieve all the rows of data from the EMP_DATA table (the SEQUENCE function takes care of this) where the columns are only what is required as specified within the curly braces (columns 1,2,3,& 7).

We have the range to filter. The criteria the filter function needs to satisfy is the CitySelected is equal to the Location column data from the employee table.

This is achieved using the SEARCH function:

In our case, this part of the formula will be:

SEARCH(CitySelected, EMP_DATA[Location])

Please note, that for case-sensitive searches, use the FIND function in place of the SEARCH function.

This will return a number for every successful match and return an error when there is no match. We’ll wrap our SEARCH result with an ISERROR function to return 0 in case of an error:

Over this, our filter condition is only when we get a match ie. for values >0. With this, our final formula will be:

The employee data with the filter based on location will look like:

This method allows HR managers to dynamically search and filter the employee database based on specific text input, such as a city name.

It’s an efficient way to handle queries that require quick access to subsets of data based on variable criteria.

This formula is used in one of our videos on creating an interactive NFL Team schedule:

If you have any feedback or suggestions, please post them in the comments below.

#### Data to Decisions

Free Google Sheet Template

### 2024 Small Business Product Catalog in Google Sheets

Download this template, If you are looking for a product catalogue to share your product info with your prospects and customers.

Read More »

### How to Create a Bubble Chart in Excel?

Learn how to create a bubble chart in Excel to visually compare three sets of data effortlessly. Perfect for data analysis and presentations.

Read More »

### How to Create a Dumbbell Chart with Scatter Plot in Excel?

Learn step-by-step how to create a dynamic Dumbbell Chart using a Scatter Plot in Excel to elegantly display ranges in values.

Read More »

## Boost Your Excel Skills!

Join our weekly newsletter for expert insights.