Concatenate values returned from array formula

Question

I have a set of columns with formulas that provides a count of cells that match my criteria, for example the following checks three different pairs of columns for matches to my two lookup criteria:

=COUNTIFS(SP_DATA!$H:$H,$D60,SP_DATA!$I:$I,F$1)**+COUNTIFS(SP_DATA!$K:$K,$D60,SP_DATA!$L:$L,F$1)+**COUNTIFS(SP_DATA!$N:$N,$D60,SP_DATA!$O:$O,F$1)

On the SP_Data sheet, some of the cells will have matching values, some will have non-matching values, and some will be blank

Now I would like to provide a concatenated list of values from SP_Data!B:B from those rows where matches were found.

My first thought was to use an array formula (since the information are text strings, and other formulas like sumifs and countifs won't give me what I need). I tend to build formulas up from the basics to make sure they work (in this case, each of the three statements substatements that were totalled above); each of these three are entered as array formulas, for example:

=CONCATENATE(IF(AND(SP_DATA!$H$1:$H$10000=$D60,SP_DATA!$I$1:$I$10000=F$1),SP_DATA!$B$1:$B$10000,""))

However, I don't get results under any of the three, even though I know I have a match for columns H/I.

When I use the 'evaluate formula' to review these, I can find the instances of TRUE in the IF statement, but the very next step in the evaluation shows =concatenate("").... in other words, the AND statement appears to be applied to all of the criteria within the ( ) rather than on an row-by-row basis.

I can retrieve the text result when I narrow my formula down to a row where I know there is a match, e.g.:

=CONCATENATE(IF((SP_DATA!$H$21=$D60)*(SP_DATA!$I$21=F$1),SP_DATA!$B$21,""))

[I also removed the AND statement, but that alone didn't get me a result until I limited the number of rows to just one]

I then added a second row that doesn't have a match:

=CONCATENATE(IF((SP_DATA!$H$21:H22=$D60)*(SP_DATA!$I$21:I22=F$1),SP_DATA!$B$21:B22,""))

and it still returned the expected text string from B:B

but when I tried to expand the range just a little more,

=CONCATENATE(IF((SP_DATA!$H$2:H22=$D60)*(SP_DATA!$I$2:I22=F$1),SP_DATA!$B$2:B22,""))

I went back to getting nothing. I checked the raw data, and there are no blanks in this part of the range- there are 21 non-matching values and 1 matching value.

After further testing, I realized that I was only returning the target value if it was the first cell in the formula range, e.g.

=CONCATENATE(IF((SP_DATA!$H$21:H220=$D60)*(SP_DATA!$I$21:I220=F$1),SP_DATA!$B$21:B220,""))

Still returns my single matching string from B21.

Any suggested approaches to test would be greatly appreciated. My final step will be to add char(13) between each returned value inside the cell to make the list readable, but I can't even get the strings themselves yet.

Thanks in advance,

Keith

Answer 1

Hello,

I suggest to use sbCat instead of CONCATENATE:

http://sulprobil.com/html/sbcat.html

Regards,

Bernd

Answer 2

Unfortunately, CONCATENATE will only return the first element of an array.  While with just a few potential matches, it is feasible to INDEX through each element in the array, with any significant number of elements to check, a VBA solution is much more practical.

If you would like, post sample data in a workbook at some public site (e.g. Skydrive), and then post a link here so we can craft an appropriate solution.