Google Sheets - Two-way lookup pulling data from multiple sheet tabs

Question

I've been working on a sheet where I want to have and input tab and output tab. The input tab is basically a table and the output tab will be a calendar (of sorts).

In my example:

Sheet1 = input tab

Sheet2 = output tab

I wish to have a vlookup that will pull its search_key from Sheet2 (output) and search it against a range in Sheet1.

I've been messing around with the following (green cell Sheet2 in example):

=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$C$7,MATCH(B1,Sheet1!$A$2:$C$7,0),False))

I had also tried a variation of this using hlookup instead of MATCH but didn't have much luck with it.

The problem I have is that I no longer know where to place my column index. In my example sheet I have it working with a one way vlookup (blue cell Sheet2) that returns the desired value from Sheet1 (Length Column) using this index. Is it not possible to do so in the two way lookup??

Here's a link to the example: https://docs.google.com/spreadsheets/d/1_nqH-XOxNhQAUVJzesNBZeMci7AV9RowSQUnptAruPc/edit?usp=sharing

Iamblichus Iamblichus · Accepted Answer · 2019-09-26T09:08:42

Try running this function in Apps Script:

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');
  var firstRow = 2;
  var numRows = sheet1.getLastRow() - 1;
  var firstCol = 1;
  var numCols = sheet1.getLastColumn();
  var inputData = sheet1.getRange(firstRow, firstCol, numRows, numCols).getValues();
  var numBrands = sheet2.getLastRow();
  var outputRange = sheet2.getDataRange();
  var outputData = outputRange.getValues();
  // Iterating through each row in Sheet1 data:
  for(var i = 0; i < numRows; i++) { 
    // Iterating through each row in Sheet2:
    for(var j = 1; j < outputData.length; j++) { 
      // Iterates through each cell for each row in Sheet2.
      for(var k = 1; k < outputData[0].length; k++) { 
        var inputBrand = inputData[i][0];
        var outputBrand = outputData[j][0];
        var inputDate = inputData[i][1];
        var outputDate = outputData[0][k];
        // It checks whether the date and brand corresponding to each cell 
        // (same row or column) matches the date and brand in the current 
        // row in Sheet1
        if(inputBrand == outputBrand && inputDate.toString() == outputDate.toString()) {
          var inputLength = inputData[i][2];
          sheet2.getRange(j+1, k+1, 1, 1).setValue(inputLength);
        }      
      }  
    }
  }
}

Google Sheets - Two-way lookup pulling data from multiple sheet tabs

1 Answers