## Example

```
{=TEXTJOIN("",TRUE,IF($C$3:$C$11=E3,$B$3:$B$11,""))}
```

## Generic Formula

```
{=TEXTJOIN(Delimiter,TRUE,IF(ConditionRange=Condition,Range,""))}
```

**Note**: This is an

**array formula**. Do not type out the

**{}**brackets. Hold Ctrl + Shift then press Enter while in

**Edit Mode**to create an

**array formula**. For Mac, use ⌘ + Shift + Return.

**Range**– This is range of values which we want to concatenate together.**Delimiter**– This is the delimiter value which we want to use to separate values by in our concatenation. Use empty quotes if we don’t want to use a delimiter.**ConditionRange**– This is the range of values which we will use to test whether or not to concatenate an item from our**Range**.**Condition**– This is the condition to test.

## What It Does

This formula will conditionally concatenate a range based on a criteria in another range.

## How It Works

IF(ConditionRange=Condition,Range,””) will create an array containing data from the **Range** when it meets the given condition. In our example this will create the following array.

```
{"";"E";"X";"";"C";"";"E";"L";""}
```

TEXTJOIN(Delimiter,TRUE,Array) will concatenate the individual items in the **Array** and separating them with the chosen **Delimiter**. Using **TRUE** as the middle argument will skip any blank cells in the array. In our example TEXTJOIN(“”,TRUE,{“”;”E”;”X”;””;”C”;””;”E”;”L”;””}) results in **EXCEL**!

Hi John, This solves a similar problem I’m trying to solve in my own excel spreadsheet. I am not familiar with array formula. Can you expand on you How To Conditionally Concatenate A Range instructions for those who are new to array formulas?

Need to put commas as well in the output.

Kindly help. ๐

Use the delimiter

`","`

I have this a shot, but got a “name” error code. I tried downloading your example file and it also had the same error…?

I tried this where the range and condition is over 100k records and receiving a #VALUE! results.

Not 100% sure, but it might be because a cell can only contain 32k characters.

it doesnโt work at all, not even your exact example. I wonder why

Did you create the formula with Ctrl Shift Enter?

It works now!!! I mean MY MISTAKE! (yep, I missed that detail) that was it! Y

ou are my new hero! Thanks Sir. you really saved me from a lot of work and using VBA didnโt make sense

Yes, there are usually many ways to do something in Excel, just depends on the situation which is best.

Glad I could be a “hero” today ๐

Brilliant! I was not familiar with array formulas. (I also excitedly rushed in without reading the ctrl+shift+enter instruction).

Eventually I got this working just as I wanted.

Thanks!