Google-sheets – Google Sheets moving to a new column if cell not blank

With the following little piece of code you can accomplish that.

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find

search

result

Example

I've created an example file for you: get cell row and column index

Then you need to add criteria to exclude those in Columns D and C that are blank:

=ARRAYFORMULA(MIN(IF((A:A=H12)*(D:D<>"")*(C:C<>""), (D:D-C:C))))

The * acts like AND. So all three need to resolve to TRUE for it to consider the array in the MIN().