The following image shows you data in column B and C. I want to concatenate adjacent value to size "M" into cell F4. They are "CD", "IJ" and "OP", in picture below.

I highly recommend using the TEXTJOIN function if you own Excel 2016. It is what the CONCATENATE function should have been from the beginning:

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 […]

Instructions

  1. Copy (CTRL + c) this formula:
    =TRANSPOSE(IF(B3:B11=F3,C3:C11,""))
  2. Double click on cell F4
  3. Paste (Ctrl + v) formula to cell F4
  4. Select the entire formula in the formula bar
  5. Press Function key F9 and the formula is converted to an array of constants:
    ={"","CD","","","IJ","","","OP",""}
  6. Delete the equal sign = in the formula bar and then press Enter
    {"","CD","","","IJ","","","OP",""}
  7. Select cell F4
  8. I am now going to delete all empty characters in the array, press CTRL + H to open "Search and Replace" Dialog box
  9. Type in Find what: "", and nothing in Replace with:
  10. Click on "Replace" button
  11. Type in Find what: ,"" and nothing in Replace with:
  12. Click "Replace" button once again
  13. Click Close button

Add all values in array

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

  1. Double click on cell F4
  2. Delete the curly brackets from the formula: {}
  3. Add this: =CONCATENATE(
  4. and then an ending parentheses )
  5. Press Enter

Add delimiting character

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:

  1. The array looks like this: ={"","CD","","","IJ","","","OP",""}
  2. Add an ampersand and then a delimiting character with quotation marks.
    ={"","CD","","","IJ","","","OP",""}&"|"
  3. 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.
  4. Continue with step 6 above. Tip! To delete empty values in array, Search and Replace with these values "|", and ,"|"

User defined function

If you think the above instructions are too tedious and want something quicker and easier, check out this UDF. You can find it near the bottom of this article:

TEXTJOIN function

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

Download excel *.xlsx file

Concatenate a cell range using a condition.xlsx

Recommended article

Quickly concatenate values into one cell [No VBA]

Joining many many cell values in excel is not easy. The Concatenate function allows you to only reference a single […]