There’s no CONCATENATEIF function, and CONCATENATE itself can’t take an array, so the combination is built with IF inside CONCAT or TEXTJOIN. TEXTJOIN is the better choice because it adds a delimiter and skips blanks: =TEXTJOIN(", ",TRUE,IF(A1:A4="A",B1:B4,"")) joins every value in column B where column A says “A”.
The IF returns the matching values and empty strings for the rest, and the TRUE tells TEXTJOIN to ignore those empties. Use CONCAT instead if you want the values run together with no separator: =CONCAT(IF(A1:A4="A",B1:B4,"")).
On Excel 2019 or earlier both need to be entered with Ctrl+Shift+Enter, since IF is working across a range; on Excel 365 and 2021 a plain Enter is enough. In 365 there’s a cleaner version worth preferring: =TEXTJOIN(", ",TRUE,FILTER(B1:B4,A1:A4="A")) — FILTER does the selecting, which reads much closer to the intent and avoids the array-entry question entirely.
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.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.