Google Sheets – Rotate and Duplicate Row Values

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

Assuming your data is in the upper-left corner of a sheet, the following works (linebreaks added for readability):

=arrayformula(
  regexreplace(
   regexreplace(
    if(len(A2:A), A1 & ":" & A2:A, "") 
     & ";" & 
    if(len(B2:B), B1 & ":" & B2:B, "") 
     & ";" & 
    if(len(C2:C), C1 & ":" & C2:C, ""), 
   ";+", ";"), 
  "^;|;$", "")
 )

Each if processes one column of data, prepending values by headers if the value is nonempty. The ; are inserted in between.

The regexreplace perform a cleanup. The first one replaces any sequence of semicolons by a single semicolon. The second removes the semicolon at the beginning or end of string.

Although it's tempting to try join(";", ...) here (which wouldn't require the cleanup), it can't handle a two-dimensional array of data.