How to Concatenate Values for IF Condition in Google Sheets

In Google Sheets, you can combine cells by using some functions. CONCATENATE, JOIN and TEXTJOIN are the most popular among them. To apply some conditions with the combination you can add the IF and QUERY functions. In this article, we will discuss CONCATENATE IF in Google Sheets which can make your work easier.

Table of Contents

A Sample of Practice Spreadsheet

You can download the practice spreadsheet from the download button below.

Concatenate Values for IF Condition

3 Ways to Concatenate Values for IF Condition in Google Sheets

There are three ways to concatenate values for the IF condition in Google Sheets. To show the methods we develop a dataset that contains columns: First Name, Last Name & Sales.

1. Inserting CONCATENATE and IF Functions

You can use the CONCATENATE and IF functions combined. In that case, the CONCATENATE function will use as the value_if argument of the IF function. So, to apply the method,

First, create a result column where we want to show the Full Name if Sales > $500.

Then select cell F5 and insert the IF function.

Now add the logical_expression for the function Here, as we want to show the name whose sales are up to $500 so we add D5>500.D5 represents the first sales cell.

After that, input the CONCATENATE function as a value_if_true argument.

Now, add the first string of the CONCATENATE function. Here, we add B5 as string1.

In the next step, add a space as sting2. To add space, we use the symbol “ “.

We also add another string which is cell C5. For CONCATENATE function you can add as many strings as you want.

Now we close the bracket for the CONCATENATE function and its uses as the value_if_true argument for the IF function.

For the value_if_false parameter, we input a text to specify the values. Here we write down “Not Applicable”.

In the end, press ENTER to apply the function and you will find the desired value in the selected cell.

=IF(D5>500,CONCATENATE(B5,” “,C5),”Not Applicable”)

Finally, you can drag down the Fill handle to apply the function to the entire column and you will find the “Not Applicable” quote where the sales in less than 500.

Alternative Approach with ARRAYFORMULA

You can apply this same IF conditions for the ARRAY FORMULA function. But the difference between these two methods is here we do not use the CONCATENATE function for linking. Instead of that we just use the ‘&’ symbol to connect cells. To do so,

First Create the same result column Full Name if Sales>$500 and select cell F5.

Then apply the ARRRAYFORMULA function.

Then insert the IF function in the cell.

After that add D5:D9>500 as logical_expression.

Then input the B5:B9&” “&C5:C9 as value_if_true parameter. Using this, we’ll not require the CONCATENATE function.

Then insert the sentence “Not Applicable” as the value_if_false argument.

Finally, press ENTER to apply the function.

=ARRAYFORMULA(IF(D5:D9>500,B5:B9&” “&C5:C9,”Not Applicable”))

Similar Readings

2. Using QUERY Formula

If you want to extract only the names that fulfill the conditions, then you can apply the QUERY formula.

For the QUERY formula, you can use the same dataset and can extract the Full Name if Sales >500.

To do so, you have to insert the following complex formula with QUERY formula in cell F5 and extract the desired results in the entire column F5:F9.

=TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(QUERY(B5:D9,"select B,C where D>500"))),,COLUMNS(UNIQUE(QUERY(B5:D9,"select B,C where D>500")))))

Formula Breakdown

QUERY(B5:D9,“select B,C where D>500”): Returns values from columns B and C that match the condition “>500” in column D.
UNIQUE(QUERY(B5:D9,“select B,C where D>500”)): Outputs only unique values from the query.
TRANSPOSE(UNIQUE(QUERY(B5:D9,“select B,C where D>500”))): Used as the range of the QUERY formula.
COLUMNS(UNIQUE(QUERY(B5:D9,“select B,C where D>500”))): Represents headers.
,,: The empty query field returns all applicable data.
TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(QUERY(B5:D9,“select B,C where D>500”))),,COLUMNS(UNIQUE(QUERY(B5:D9,“select B,C where D>500”))))): transposes all the results to be presented as individual rows, otherwise all the results would have been presented in a single row.

3. Applying TEXTJOIN Function

To link the first and last name you can also apply the TEXTJOIN function. It is the alternative to the CONCATENATE function.

After applying the function in cell F5, we will find the following output,

=IF(D5>500,TEXTJOIN(“ “,TRUE,B5,C5), “Not Applicable”)

Formula Breakdown

TEXTJOIN(“ “,TRUE,B5,C5): join the cell B5 and C5.
D5>500: represents the value that exceeds sales of $500.
IF(D5>500,TEXTJOIN(“ “,TRUE,B5,C5), “Not Applicable”): It present two conditions. For TRUE argument it returns the full name or for FALSE it returns the “Not Applicable” text.

Things to Remember

Always use double quotations to add any type of text.
Insert the range carefully.
Use the fill handle to apply the function to the entire column.

Conclusion

We believe this article will help you to understand how to concatenate values for IF condition in Google Sheets. To explore more about Google Sheets, visit the OfficeWheel website.