Match row that contains text text string; list associated values from pivot table

Hi everyone,

Please refer to the attached workbook for reference.

I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.

I appreciate your help!

Thanks and best regards,
ACurtis802

This was a little convoluted. First, we need to use a trick to identify which row to use, then use and INDIRECT formula to construct a range reference from it.

=MATCH(Sheet1!C2&"/"&Sheet1!A2,Sheet2!$A$1:$A$100,0)

This matches the Factory Name and Partner Code against the picot table on Sheet2 and returns the row number.

Now, we wrap that into an ADDRESS() function to convert the answer to a beginning cell reference:

=ADDRESS(MATCH(Sheet1!C2&"/"&Sheet1!A2,Sheet2!$A$1:$A$100,0),2)
...which resolved to $B$3 in cell E2.

Now we do that AGAIN with some concatenation to create the whole range:

=ADDRESS(MATCH(Sheet1!C2&"/"&Sheet1!A2,Sheet2!$A$1:$A$100,0),2)&":"&ADDRESS(MATCH(Sheet1!C2&"/"&Sheet1!A2,Sheet2!$A$1:$A$100,0),22))
...which resolved to $B$3:$V$3 in cell E2...so now we know the range to match the 1's in.

To get your final result, we need a StringConcatenation tool that allows a variable test to be applied. I installed Chip Pearson's StringConcat() function into your sheet to accomplish this.

The final resulting ARRAY formula is:

=stringconcat(", ",IF(INDIRECT("Sheet2!"&ADDRESS(MATCH(Sheet1!C2&"/"&Sheet1!A2,Sheet2!$A$1:$A$100,0),2)&":"&ADDRESS(MATCH(Sheet1!C2&"/"&Sheet1!A2,Sheet2!$A$1:$A$100,0),22))=1,Sheet2!$B$2:$V$2,""))

Notice we wrapped our long formula above into an INDIRECT() so that it becomes an active range instead of a text string. Then we compare that range looking for 1s. If a 1 is found, it String-Concatenations the Brands separating by a comma.

Those array have to be activated with a CTRL-SHIFT-ENTER...and braces { } will appear around your formula and results will appear. If you just press ENTER the cell will go blank because the array is broken.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]