Google Sheets

How to Use FILTER and EXACT Functions in Google Sheets with Example

If you’re working with a large dataset in Google Sheets and need to filter specific results, especially when exact matching is essential, the combination of the FILTER and EXACT functions is incredibly powerful. In this blog post, we’ll break down how to use these functions step-by-step in an example so you can start applying them to your own data.

What are FILTER and EXACT Functions in Google Sheets?

Before diving into the example, let’s briefly discuss what these two functions do.

  • FILTER Function: This function returns a filtered version of the source range, returning only the rows or columns that meet the conditions you specify.
  • EXACT Function: This function compares two text strings and returns TRUE if they are exactly the same, including case sensitivity.

When you combine these functions, you can filter data with case-sensitive criteria—perfect for instances when “a” and “A” are not the same in your dataset.

Example Dataset

For this example, let’s consider a table containing names, groups, and scores. Here’s the sample dataset that we’ll use:

We want to filter out the rows where the group is exactly “a” (lowercase).

FILTER and EXACT Functions
FILTER and EXACT Functions

Applying the FILTER and EXACT Formula

Now, let’s write the formula to filter the data based on the group “a”. We’ll use the following formula:

=FILTER(B4:D13,EXACT(C4:C13,C15))

Let’s break this down for you:

  • FILTER(B4:D13,EXACT(C4:C13,C15)): This formula filters the range from B4 to D13 (which includes the Name, Group, and Score columns) based on the condition provided in the EXACT function.
  • EXACT(C4:C13,C15): This part of the formula checks if each value in the Group column (C4 to C13) is exactly equal to the value in cell C15, which in this case is “a”. Remember, EXACT is case-sensitive, so only groups marked with a lowercase “a” will be filtered.

Formula Output

After applying the formula, here is the output we get:

As you can see, only the rows where the group is exactly “a” are returned. This is a simple but effective way to ensure your data filtering is precise, especially when working with case-sensitive fields.

FILTER and EXACT Functions
FILTER and EXACT Functions

Why Use the FILTER and EXACT Functions Together?

Using FILTER and EXACT together offers several benefits:

  • ✨ Case Sensitivity: If your data requires exact matches, including upper and lower case, EXACT ensures no mismatches slip through.
  • ✨ Dynamic Filtering: The FILTER function allows you to change your filtering criteria easily by just updating one cell (in our case, cell C15).
  • ✨ Efficient Data Handling: Combining these two functions lets you handle large datasets more efficiently, returning just the information you need without manual sorting or filtering.

Final Thoughts

This combination of functions is an excellent tool to have in your Google Sheets toolkit. Whether you’re working with case-sensitive data or simply need to streamline your filtering process, learning how to use the FILTER and EXACT functions will save you time and effort.

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@NeotechNavigators

 

Click here to Make the copy of this Template

PK
Meet PK, the founder of NeotechNavigators.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your data analysis skills to the next level!
http://neotechnavigators.com

Leave a Reply