In Excel, there are two methods to use concatenate if formula.
- By Column
- By Rows
And we have two different functions to write these formulas. So, in this tutorial, we will learn all four methods.
Concatenate with IF using CONCAT Function
1. By Column
This formula is quite simple. Here we are testing whether the value “A” is in column A. And if it is there, combine values from columns A and B.
We have used functions CONCAT and IF. In the IF function, we have specified a condition to test whether the value in cell A1 is “A”.
And if the value is A, then return the range A1:B1; otherwise, a blank value. In the end, CONCAT uses the range returned by IF and combines values from it.
=CONCAT(IF(A1="A",A1:B1,""))2. By Rows (+ Columns)
Now let’s say you want to concatenate values from all rows using a condition. In this case, you can use the same formula but must refer to the entire range.
=CONCAT(IF(A1:A4="A",A1:B4,""))In this formula, IF checks for the value A” from the entire range A1:A4. TRUE for the cell with the value and FALSE for the rest.
And then, it returns the rows from the range A1:B4 where the value is “A” in column A.
Concatenate with IF using TEXTJOIN Function
1. By Columns
Once you insert the TEXTJOIN function, you need to specify the delimiter. And also whether you want to ignore blank cells or not. After that, you need to use the IF function as you have used in the previous method.
=TEXTJOIN(",",TRUE,IF(A1="A",A1:B1,""))IF only returns the values from the range if a cell in column A has the value “A”.
2. By Rows
And in the same way, you can concatenate values from multiple rows using a condition. For example, in the below formula, you have referred to the entire range A1:A4 for the condition to test. And to the range A1:B4 for value to combine.
=TEXTJOIN(",",TRUE,IF(A1:A4="A",A1:B4,""))
In the result, we have the values A,1,A,3, from the cells A1, B1, A3, and B3. With the IF function, we have tested a condition in column A. It returns the values for the rows where the cell value in column A is “A”.
TEXTJOIN ignores the blank values and, using the delimiter combines all the values as one.