Google-sheets – Make each cell in a column update from the same row of another sheet

formattinggoogle sheets

I'm looking for a function I could apply to a column that would make each cell in the column check the value of the first cell in its row and the first cell in its column and then return a value from the same cell of another sheet (matching first-cell-of-row + first-cell-of-column values).

For instance:

In sheet #1 the value of cell "B5" is "purple"; the value of the first cell in column "B" is "red"; the value of the first cell in row "5" is "blue".
In sheet #2, the value of the first cell in column "B" is "red"; the value of the first cell in row "3" is blue"; I'd like a function to make cell "B3" return the value "purple" automatically—except I'd like to apply this function to the entire column

Is this possible? If you have any questions about my example or my question, please ask and I'll try to clarify. Thanks!

Best Answer

You can use index() and match(), along with absolute references for this. I don't know the details of the first row and column in either sheet so I assume that they will be populated by you. You can then put a formula like the following starting from B2 on sheet #2:

=iferror( index(
    'Sheet 1'!$A:$N,
    match($A2, 'Sheet 1'!$A:$A, 0),
    match(B$1, 'Sheet 1'!$1:$1, 0)
))

This formula simply pull the cell from 'Sheet 1'!$A:$N with matching first row and column. You will need to update the range accordingly if your data on sheet 1 goes beyond column N.

In the row matching function, you want to make the column references absolute so that you will always be comparing the first column on each row when you drag the formula across horizontally. Likewise, in the column matching function, you want to make the row references absolute so that you will always be comparing the first row on each column when you drag the formula down.

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
enter image description here

search enter image description here

result
enter image description here

Example

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

Best Answer

Related Solutions

Google-sheets – The column from which to return VLOOKUP responses depends upon the value of a cell

Google-sheets – Fetch cell row and column indices in Google Sheets

Code

Explained

Screenshot

Example

Related Topic