Why I can't using arrayformula in lookup function with autofill from google form?

Question

So, right now I'm building a KPI that need to create a single flow KPI submission using Google Form to Google Sheet. You can see my example in sheet here and Google form here

First Sheet Image

Second Sheet Image

Right now I'm using lookup value from 3 criteria (with INDEX and MATCH function) that i made but here's the problem. I know that google form will create a new row under the existing row instead of replicate it when a submission was entered. But, i need to fill my 'E' and 'F' column (from my example) automatically, after the google form or 'A until D' column was entered.

I've tried some code using Script editor in Sheet that will generate one active column that i highlighted to drag down automatically when im running the script. This method won't be effective if i had to apply it one by one for 10-20 columns...

Here's the google script code:

function myFormula(){
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var tabs = ['Form Responses 1'];
  var columns = [5,6];
  if(tabs.indexOf(activeSheet.getName()) !== -1){
    var cell = activeSheet.getActiveCell();
    var col = cell.getColumn();
    if(columns.indexOf(col) !== -1){
      if(cell.getFormula() !== ""){
        var destination = activeSheet.getRange(2,col,activeSheet.getLastRow()-1, 1); 
        cell.copyTo(destination);
      }
    }
  }
}

So, can you guys please help me with it? I will appreciate it if you would help me with my projects. Thankyou.

If you want help with your Apps Script please give permissions for the example sheet, at the moment it is not available to access. — iansedano
Thanks for your confirmation @iansedano , however this problem has been solved by JPV. And i prefer to use google sheet equation. Once again, thankyou! — Sung

JPV JPV · Accepted Answer · 2021-02-09T09:06:37

I entered this formula in G2

=ArrayFormula(if(len(A2:A), vlookup(to_text(B2:B)&C2:C&D2:D, {to_text('Database for Each Indicator'!A:A)&'Database for Each Indicator'!B:B&'Database for Each Indicator'!C:C, 'Database for Each Indicator'!D:E}, {2, 3}, 0),))

The output of this formula should expand when new forms are submitted. See if that works for you?

Why I can't using arrayformula in lookup function with autofill from google form?

1 Answers