I have this sheet tab that contains these columns:

In another tab, I have this column:

I want the result to be like this:

I have this formula that I'm using that somewhat works:

=IFERROR(TEXTJOIN(" ",TRUE,QUERY(ARRAYFORMULA(IF(REGEXMATCH($B2,Masterlist!$A$2:$A$139),Masterlist!$A$2:$A$139,"")),"where Col1 is not null")))

but the result I'm getting is like this:

CodePudding user response：

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Sheet1");
  const [h1, ...vs1] = sh1.getDataRange().getDisplayValues();
  let obj1 = {};
  h1.forEach((h, i) => { obj1[h] = i   1; });
  const code = sh1.getRange(2,obj1["Activity Code"],sh1.getLastRow() - 1).getDisplayValues().flat().map(c => c.replace(/ /g,''));
  const sh2 = ss.getSheetByName("Sheet2");
  const [h2, ...vs2] = sh2.getDataRange().getDisplayValues();
  let obj2 = {};
  h2.forEach((h, i) => { obj2[h] = i   1; });
  const acct = sh2.getRange(2,obj2["Account"],sh2.getLastRow() - 1).getDisplayValues().flat();
  let arr = [["Activity Code","Account"]]
  acct.forEach((a,i) => 
    code.forEach(c => {
      if(~c.indexOf(a)) {
        arr.push([c,a]);
      }
    }
  ));
  Logger.log(JSON.stringify(arr));
  const sh3 = ss.getSheetByName("Sheet3");
  sh3.clearContents();
  sh3.getRange(1,1,arr.length, arr[0].length).setValues(arr);
}

Sheet1:

Sheet2:

Sheet3: