Google Sheets - Array Index Match Multiple Critera

Question

Link to Sheet: https://docs.google.com/spreadsheets/d/1_40L74r4Y-inibZw7aplE38DDzdszrFgBMX3HU_Bsf0/edit?usp=sharing

I have a raw data sheet with responses from a questionaire. From this sheet, I want to spread out the answers over multiple sheets, depending on client & project, so that we get one shipping list for each project.

In the Shipping List I added A1 as the project number and A2 as the Client name to refer to. Now I want to list all emails from the raw data sheet, that also match A1 and A2 as project & client. Also I need to fetch the "Amount Prdt" (Product_Pick_RAW!A) and "Products" (Product_Pick_RAW!B:V). All Product info should be joined together with commas as a seperator.

Status Quo: I picked out the emails with: =IFERROR(INDEX(Product_Pick_RAW!$A2:$AC; MATCH($A$1;Product_Pick_RAW!X:X;0);23);"FEHLER")

Amount Prdt: =FILTER(Product_Pick_RAW!A:A;Product_Pick_RAW!Y:Y=TRIM($A$2);Product_Pick_RAW!X:X=TRIM($A$1);Product_Pick_RAW!W:W=TRIM(E4))

Products: =INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(FILTER(IF(Product_Pick_RAW!B:U="";;Product_Pick_RAW!B:U&",");Product_Pick_RAW!Y:Y=$A$2;Product_Pick_RAW!X:X=$A$1;Product_Pick_RAW!W:W=E4));;9^9))); ",$";))

Problem: Now I realized specifically the email solution this is not usable for an array case and picking out multiple results.

Does anyone know a solution for this?

Thanks!

Answer 1

SUGGESTION

Perhaps you can try this method:

==UPDATE==

For Email on E column that's separated by row:

=QUERY(Product_Pick_RAW!W2:Y,"SELECT W where Y='"&$A$2&"' AND X='"&$A$1&"'")

==UPDATE==

For Amount Prdt on H column that's separated by row:

=QUERY(Product_Pick_RAW!A2:Y, "SELECT A WHERE Y = '"&$A$2&"' AND X = '"&A1&"'")

==UPDATE==

For Products on I column, you can try this custom function FINDLINKS by adding this script below as a bound script on your spreadsheet:

function FINDLINKS(range, project, client) {
  var container = [];
  for(x=0; x<range.length; x++){
    if(range[x][22].includes(project) & range[x][23].includes(client)){
      container.push([range[x][0]+"\n"+
                      range[x][1]+"\n"+
                      range[x][2]+"\n"+
                      range[x][3]+"\n"+
                      range[x][4]+"\n"+
                      range[x][5]+"\n"+
                      range[x][6]+"\n"+
                      range[x][7]+"\n"+
                      range[x][8]+"\n"+
                      range[x][9]+"\n"+
                      range[x][10]+"\n"+
                      range[x][11]+"\n"+
                      range[x][12]+"\n"+
                      range[x][13]+"\n"+
                      range[x][14]+"\n"+
                      range[x][15]+"\n"+
                      range[x][16]+"\n"+
                      range[x][17]+"\n"+
                      range[x][18]+"\n"+
                      range[x][19]]);
    }
  }
  return container;
}

Then you can use FINDLINKS custom function on column I like this:

=ARRAYFORMULA(TRIM(FINDLINKS(Product_Pick_RAW!B2:Y9;$A$1;$A$2)))

Sample Result: