How to Use TEXTJOIN and FILTER Functions in Google Sheets: A Practical Example

When working with Google Sheets, sometimes you need to combine data from different rows or filter it based on specific criteria. Two incredibly powerful functions that help you achieve this are TEXTJOIN and FILTER Functions. Whether you’re trying to group names based on their city or filter information based on certain conditions, these functions will make your data management much easier.

In this blog post, I’ll guide you through an easy-to-understand example of how to use both of these functions together.

Table of Contents

Why Use TEXTJOIN and FILTER Functions in Google Sheets?

You might wonder, why not just manually select and group data? Well, for small datasets, that’s possible, but when you’re working with larger sets of information, automating this process saves you time and minimizes the risk of errors. The TEXTJOIN function allows you to join values from multiple cells into one, while the FILTER function helps you pull out specific information based on criteria. Together, they offer a flexible way to filter and organize your data.

The Example We’ll Be Using

We have the following data in a Google Sheet (range A4) representing a list of employees, their cities, and their departments:

Now, we want to group the names of employees based on their city. For instance, we want to display all the employees in Delhi in one cell, separated by commas.

The Formula Breakdown

Here’s the formula we’ll be using to achieve this:

=TEXTJOIN(", ", TRUE, FILTER($A$3:$A$17, $B$3:$B$17 = B19))

Let’s break it down:

TEXTJOIN(“, “, TRUE, …): This function combines the filtered results into a single cell. The first argument “, ” specifies that the values will be separated by a comma and space. The TRUE argument tells the function to skip empty cells.
FILTER($A$3:$A$17, $B$3:$B$17 = B19): This part of the formula filters the data. The FILTER function pulls all the names from column A where the city in column B matches the city in cell B19 (for example, “Delhi”).

In simpler terms, the formula filters out the names based on the city and then joins them into a single cell, with each name separated by a comma.

The Output

Here’s what the output looks like when applying this formula to our data:

As you can see, for each city, we now have a list of employees grouped together and displayed in a single cell.

When to Use TEXTJOIN and FILTER Functions Together?

This method is particularly useful when:

You want to create a summary of data without manually sorting or copying information.

You need to organize data for reports, presentations, or analysis.

You’re working with large datasets where manually organizing data would be tedious.

Conclusion

Using the TEXTJOIN and FILTER Functions in Google Sheets is a fantastic way to automate the process of filtering and grouping data. Whether you’re dealing with employee lists, sales data, or other datasets, these functions can simplify your workflow and save you valuable time.

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

Youtube.com/@NeotechNavigators