Formula to find all matches in column A and return cells from column C

**GenericPat** · 11-19-2012, 04:30 PM

Hey guys,

I need a formula to find ALL matching cells in column A and return their respective column C cells into column D combined

For example:

A1: 123 B1: 321 C1: 999
A2: 123 B2: 356 C2: 977
A3: 123 B3: 458 C3: 955

In this case, cells A1, A2 and A3 are all equal so return "999 // 977 // 955" in cell D1

Thanks,

Pat.

**tigeravatar** · 11-19-2012, 04:56 PM

Pat,

Unfortunately, Excel's native CONCATENATE() function doesn't work with arrays. This means that there is no way Excel can perform what you're asking for without the use of VBA or an add-in. I have created a UDF for this task which I named ConcatAll. The UDF code and instructions for how to use it are located here:
http://www.excelforum.com/tips-and-t...geravatar.html

Attached is an example workbook based on the criteria you described.
Headers are in row 1 so actual data starts in row 2
Your provided sample data is in A2:C4 and there is additional sample data that goes down to row 10

In cell F1 is this formula that gets the number of unique values listed in column A:

=SUMPRODUCT(($A$2:$A$10<>"")/COUNTIF($A$2:$A$10,$A$2:$A$10&""))

In cell E4 and copied down to cell E8 is this formula that returns the unique list of values from column A:

=IF(ROWS(E$3:E3)>$F$1,"",INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF(E$3:E3,$A$2:$A$10),),0)))

In cell F4 and copied down is this array formula that uses the UDF ConcatAll in order to get the results you're looking for:

=IF(E4="","",ConcatAll(IF($A$2:$A$10=E4,$C$2:$C$10,"")," // "))

Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how the formula gets surrounded by the curly braces {}, do not try to add those manually.