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.

overview of concatenate if google sheets

A Sample of Practice Spreadsheet

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

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.

dataset of concatenate if google sheets

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.

create result column in google sheets

  • Then select cell F5 and insert the IF function.

insert 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.

insert logical expression in the function

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

add concatenate function in google sheets

insert concatenate with if function

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

add space as argument in concatenate if function

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

add another string in concatenate if function in google sheets

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

input concatenate as value in if function

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

add another argument for if function in google sheets

  • 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”)

outcome of concatenate if google sheets

  • 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.

insert the function into the entire column

Read More: How to Concatenate If Cell Is Not Blank in Google Sheets (7 Ways)

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.

create a result column

  • Then apply the ARRRAYFORMULA function.

apply arrayformula function in google sheets

  • Then insert the IF function in the cell.

input if function in google sheets

  • After that add D5:D9>500 as logical_expression.

add logical expression in google sheets

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

add true value argument

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

set a test for value if false

  • Finally, press ENTER to apply the function.
=ARRAYFORMULA(IF(D5:D9>500,B5:B9&” “&C5:C9,”Not Applicable”))
outcome of concatenate if google sheets

Read More: How to Concatenate Text and Formula in Google Sheets (7 Ways)

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")))))

final outcome of query function in google sheets

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.

Read More: Google Sheets QUERY Function to Concatenate Two Columns

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”)

outcome of textjoin function

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.

Read More: How to Append Text in Google Sheets (An Easy Guide)

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.


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.

Related Articles

Annyca Tabassum

Annyca Tabassum

Hello, I'm Annyca Tabassum. I currently work for the SOFTEKO as a technical writer and content developer. I have completed my graduation and post-graduation from SUST.

We will be happy to hear your thoughts

Leave a reply