How to Use a Concatenate If Formula in Excel

Using a Concatenate If Formula in Excel

A Concatenate If formula in Excel allows you to combine data from multiple cells based on specific criteria. In Excel, there's no built-in "Concatenate If" function, but you can achieve this by using an array formula with IF and CONCAT functions.

Here's how to use a Concatenate If formula in Excel:

1. Identify the range of cells that you want to concatenate based on a specific condition. For example, let's say you have a list of names in cells A1:A5 and a list of corresponding scores in cells B1:B5. You want to concatenate the names of people who scored above 70.
2. Use the IF function to create an array formula based on the condition. In this example, the condition is that the score is above 70. The formula would look like this:

=IF(B1:B5 > 70, A1:A5, "")

3. Next, use the CONCAT function to concatenate the names that meet the condition. Wrap the above IF formula inside the CONCAT function like this:

=CONCAT(IF(B1:B5 > 70, A1:A5, ""))

4. Since this is an array formula, you need to enter it as an array formula. To do this, click on the cell where you want the result to appear, type the formula, and press Ctrl + Shift + Enter instead of just pressing Enter. Excel will automatically add curly braces {} around the formula, indicating it's an array formula.

That's it! The Concatenate If formula will now combine the names that meet the specified condition.

Example

Let's see an example to understand how to use a Concatenate If formula in Excel.

1. In cells A1:A5, enter the following names:

John
Jane
Sam
Sara
Tom

2. In cells B1:B5, enter the following scores:

85
65
75
55
90

3. In cell C1, type the following formula:

=CONCAT(IF(B1:B5 > 70, A1:A5 & ", ", ""))

4. Press Ctrl + Shift + Enter to enter the formula as an array formula.

In cell C1, you should now see the concatenated names of people who scored above 70: "John, Sam, Tom,".