## Concatenate cell values based on a condition [No VBA]

This article is for Excel users that don't have the latest Excel version or can't or don't want to use VBA code.

The image above shows you data in column B and C. I want to concatenate values on the same row as the condition specified in cell F2 which is in this case "M".

The corresponding values are "CD", "IJ" and "OP", in the picture above.

**Table of Contents**

I highly recommend using the TEXTJOIN function if you use at least Excel 2019 or a later version.

For earlier versions than Excel 2019 use the user-defined function demonstrated here: Lookup and return multiple values concatenated into one cell

It is what the CONCATENATE function should have been from the beginning.

## 1. Create an array of values manually

- Copy (CTRL + c) this formula:

=TRANSPOSE(IF(B3:B11=F3,C3:C11,"")) - Double press with left mouse button on cell F4
- Paste (Ctrl + v) formula to cell F4
- Select the entire formula in the formula bar

- Press Function key F9 and the formula is converted to an array of constants:

={"","CD","","","IJ","","","OP",""} - Delete the equal sign = in the formula bar and then press Enter

{"","CD","","","IJ","","","OP",""} - Select cell F4

### 1.1 Explaining formula

#### Step 1 - Logical expression

The equal sign is a logical operator that allows you to compare value to value. In this case, we are going to compare a single value to multiple values.

B3:B11=F3

becomes

{"S";"M";"L";"S";"M";"L";"S";"M";"L"}="M"

The result is TRUE or FALSE based on if the value is equal or not. TRUE and FALSE are boolean values that can be used in a IF function.

{"S";"M";"L";"S";"M";"L";"S";"M";"L"}="M"

returns

{FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.

#### Step 2 - IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(B3:B11=F3,C3:C11,"")

becomes

IF({FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {"AB";"CD";"EF";"GH";"IJ";"KL";"MN";"OP";"QR"},"")

and returns {"";"CD";"";"";"IJ";"";"";"OP";""}.

#### Step 3 - TRANSPOSE function

The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa. The CONCATENATE function needs the comma as a delimiting character to function properly.

TRANSPOSE(IF(B3:B11=F3,C3:C11,""))

becomes

TRANSPOSE({"";"CD";"";"";"IJ";"";"";"OP";""})

and returns

{"", "CD", "", "", "IJ", "", "", "OP", ""}.

## 2. Delete blanks in the array

- I am now going to delete all empty characters in the array, press CTRL + H to open "Search and Replace" Dialog box

- Type in Find what: "", and nothing in Replace with:
- Press with mouse on "Replace" button

- Type in Find what: ,"" and nothing in Replace with:
- Press with left mouse button on "Replace" button once again.
- Press with left mouse button on Close button.

## 3. Concatenate all values in the array

The final thing to do is to use the concatenate function to add all values into one text string.

- Double press with left mouse button on cell F4
- Delete the curly brackets from the formula: {}
- Add this: =CONCATENATE(
- and then an ending parentheses )

- Press Enter

## 4. Add delimiting characters

Perhaps you want a delimiting character between values, right after you have converted the formula to an array of constants (step 5, above), do this:

- The array looks like this: ={"","CD","","","IJ","","","OP",""}
- Add an ampersand and then a delimiting character with quotation marks.

={"","CD","","","IJ","","","OP",""}&"|" - Select the formula and press function key F9, the formula now looks like this:

={"|","CD|","|","|","IJ|","|","|","OP|","|"}

The ampersand has added the delimiting character to all values in the array. - Continue with step 6 above.
**Tip!**To delete empty values in array, Search and Replace with these values "|", and ,"|"

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

Concatenate unique distinct values

This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]

Use a drop down list to filter and concatenate unique distinct values

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]

### 2 Responses to “Concatenate cell values based on a condition [No VBA]”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

you have taught this for M, if i replace M with S in F3 then it's not changing dynamically..please suggest something

This article shows the steps you need to take in order to manually concatenate the values if your Excel version does not support the TEXTJOIN function (Office 365).

The following article demonstrates how to do this dynamically using the TEXTJOIN function:

https://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/#concatenate