What Is TEXTJOIN IF in Google Sheets?
TEXTJOIN IF in Google Sheets is an instrumental combination that allows you to join multiple text values based on a specific condition. As known to many of us, TEXTJOIN is used to concatenate text from multiple cells. When we add the IF function within it, the function filters the values before they get joined together. To apply this function, you may have to enter it as an array formula in Google sheets (press Ctrl + Shift + Enter) when we are dealing with ranges.
In the example below, we use TEXTJOIN IF we have a few names and their ages and we wish to join the names of those aged 15, separated by commas, we use the following TEXTJOIN IF in Google Sheets combination.
=ARRAYFORMULA(TEXTJOIN(“, “, TRUE, IF(B1:B5 = 15, A1:A5, “”)))
Thus, this function combines the text from multiple arrays, with a specifiable delimiter separating them.
Key Takeaways
- The function combination TEXTJOIN IF in Google Sheets is powerful and allows you to join and combine multiple text values into a single cell based on a certain condition. Here, TEXTJOIN is used to join text from multiple cells, and the IF function helps filter the values based on any condition.
- The syntax of the TEXTJOIN is as follows:
=TEXTJOIN(delimiter, ignore_empty, condition_range, condition, [text_range])
- This function has the condition placed as the third argument.
- If ignore_empty is set to TRUE, the empty strings will not be included in the result.
- The combination of TEXTJOIN and IF should be entered as an array formula; this is done by pressing Ctrl + Shift + Enter or wrapping the function in ARRAYFORMULA. However, pressing Enter is sufficient for a single row or column.
Syntax
Let us look at the syntax of the TEXTJOIN function. The TEXTJOIN IF Google Sheets formula is:
=TEXTJOIN(delimiter, ignore_empty, condition_range, condition, [text_range])
Arguments:
- delimiter – A string or a reference to a valid string. If empty, the text will be concatenated.
- ignore_empty – we enter a BOOLEAN value. If TRUE, empty cells in the text arguments won’t be included in the result.
- condition_range – Any text which may be a string, or an array of strings to apply the condition to.
- condition – The condition you want to test
- text2, … [OPTIONAL] – any additional text.
We use the IF function in the condition part of TEXTJOIN. We also use ARRAYFORMULA to apply it to the entire range.
For instance=ARRAYFORMULA(TEXTJOIN(“|”,TRUE,(IF(B1:B5=”Red”,A1:A5,””)))). We check the range B1 to B5 for the color “Red” and print the corresponding values in cells A1:A5 by joining them and separating using pipes. |
How To Use TEXTJOIN IF Function in Google Sheets?
We can use TEXTJOIN IF in Google Sheets in different ways. Let us look at a simple example of how to use the TEXTJOIN with an IF function in Google Sheets.
In the sheet below, we have entered the names of some students in column A and their scores in Column B. We must join the names of all students who scored above 80 and separate the names with a space.
Step 1: Go to the Google Sheet containing the data. Select a cell where you want to enter the formula.
Step 2: In the selected cell, type the following formula:
=ARRAYFORMULA(TEXTJOIN(” “, TRUE, IF(B2:B11 > 80, A2:A11, “”)))
Here, TEXTJOIN(” “, TRUE, …) means that we have to join the text with a space as the separator and TRUE tells it to ignore empty values (TRUE).
IF(B2:B11 > 80, A2:A11, “”): This part checks if the score is greater than 80 in the range B2 to B11. If so, it returns the name from A2 to A11. Otherwise, it returns an empty string “”.
Wrap it in ARRAYFORMULA to apply the formula to the entire range.
Step 3: Press Enter. The formula will return the following result as shown below.
Examples
The combination of TEXTJOIN and IF is a powerful one whenever you are looking to concatenate a range separating the values by a comma, semicolon or any other delimiter. Let us look at some examples on the same.
Example #1
As TEXTJOIN can handle arrays of text values, we can use it to merge the contents of two or more cells based on set conditions. One uses the IF function to set these conditions to evaluate a range of cells and return an array of text that satisfies the condition specified in the TEXTJOIN argument. There are some members belonging to different clubs in a city. We wish to retrieve the members of a particular club and display them.
Step 1: From the table shown below, suppose we wish to retrieve the members of Club Casa. To achieve this, nest the following IF statement into the condition argument shown in the syntax above. It is as:
IF(B2:B9=”Casa”, A2:A9, “”)
This means that for the values in column B that equals “Casa”, it returns a value from column A in the same row; otherwise, it returns an empty string.
The complete formula with TEXTJOIN is as follows:
=ARRAYFORMULA(TEXTJOIN(” “, TRUE, IF(B2:B9 = “Granda”, A2:A9, ” “)))
Step 2: Press Enter. You get the members of team Casa displayed in the specified cell separated by commas.
Example #2 – Concatenate Cells Based on a Condition (Task Status)
Let us walk through an example where we use TEXTJOIN and IF to concatenate cell values based on a specified condition in Google Sheets. In this example, we have the names of employees in Column A and their task status in Column B. We will concatenate the names of tasks that are “In progress.”
Step 1: Enter all the data in a sheet as shown below.
Step 2: Click on an empty cell where you wish the result to be displayed.
Enter the Formula: In the selected cell, type the following formula:
=ARRAYFORMULA(TEXTJOIN(“,”, TRUE, IF(B2:B6 = “In progress”, A2:A6, “”)))
Explanation:
IF(B2:B9 = “Completed”, A2:A9, “”): This part of the formula checks if the status in column B is equal to “In progress.” If true, it returns the corresponding name from column A. If the condition is false, it returns an empty string, which is given as the last argument.
TEXTJOIN(“,”, TRUE….) – This part separates the results with a comma and the TRUE ignores empty cells.
After entering the formula, press Enter.
Step 3: The formula will return the following result. These are the tasks that have a status of “In progress.”
Example #3 – Concatenate Cells Based on a Text Condition (Product Category)
In this TEXTJOIN IF Google Sheets multiple criteria example, we are concatenating cells based on a product category. We will try this example in a different way where we combine the values of multiple cells with multiple conditions. For this, we utilize the TEXTJOIN and IF functions together. To evaluate multiple criteria, we will use IF’s logical text and nest them using the asterisk (*) for the AND operator. Thus, the logical test will return TRUE only if all the conditions are met.
In our example, we will create a space-separated list of product names for a seller belonging to a particular region.
Step 1: Make a table with all your data as shown below.
Step 2: Enter the following formula in an empty cell.
=ARRAYFORMULA(TEXTJOIN(” “, TRUE, IF((B2:B9=”A”)*(C2:C9=”North”),A2:A9, “”)))
IF((B2:B9=”A”)*(C2:C9=”North”) – We check for multiple conditions. Here, we check if the range in Column B belongs to the product category A. We use ‘*’ as the AND operator and check if the range in Column C contains “North” as the region.
TEXTJOIN(” “, TRUE, IF((B2:B9=”A”)*(C2:C9=”North”), A2:A9, “”) – Here, TEXTJOIN checks both the conditions specified above and returns the corresponding values from column A for those rows which come as TRUE. The values are separated by a space as specified by the first argument of TEXTJOIN.
ARRAYFORMULA is needed as we use an entire range for the example.
Step 3: Press Enter and obtain the result.
Important Things to Note
- When we combine IF and TEXTJOIN and use them with ranges, we must press Ctrl + Shift + Enter to make it an array formula. You can also wrap the formula with ARRAYFORMULA.
- If the ignore_empty(second) argument in TEXTJOIN is set to FALSE, the empty strings will be included in the combined result and may even cause the result to have unnecessary delimiters.
- Always keep the IF statement correctly within TEXTJOIN and it should wrap the entire IF statement that handles the logical condition for accurate results.
- To keep the column or row fixed when dragging the formula, we can use $ to get an absolute reference. For e.g. =ARRAYFORMULA(TEXTJOIN(” “, TRUE, IF($B$2:$B$9 = “Granda”, $A$2:$A$9, ” “)))
Frequently Asked Questions (FAQs)
When we wish to combine the values in multiple cells in ranges with multiple conditions, we utilize the TEXTJOIN function wrapping the IF function. To evaluate multiple criteria, We can nest the logical conditions within the IF statement by separating them using () as the AND operator. Thus, the logical test will return TRUE only if all the conditions are met. For example, =ARRAYFORMULA(TEXTJOIN(” “, TRUE, IF((B2:B9=20)(C2:C9=”Big”),A2:A9, “”))). Here, two conditions have been specified in the IF function separated by *.
The TEXTJOIN IF functions when combined work the same in Google Sheets and Excel. However, we have to type the ARRAYFORMULA Function manually to wrap the two functions. Otherwise, it can be automatically added by pressing the keys CTRL + SHIFT + ENTER after entering the TEXTJOIN-IF formula.
To ignore empty cells when using the TEXTJOIN function with IF, just make the ignore_empty parameter of TEXTJOIN as TRUE. The syntax of the TEXTJOIN function is =TEXTJOIN(delimiter, ignore_empty, condition_range, condition, [text_range]).
As seen above, this means the second argument should be set as TRUE. This ensures that any empty cells are not included in the result.
Download Template
This article must help understand TEXTJOIN IF in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is TEXTJOIN IF in Google Sheets. We learn its syntax & how to use it to join text with conditions using stepwise examples. You can learn more from the following articles. –
Leave a Reply