Help with Index/match using two columns against another sheet

[JMR...]

Hi,

I am having issue getting the adjacent value if there is match. I am wondering if someone could check my code below and help me capture the correct value.

TIA!

var ss = SpreadsheetApp.getActive();

var sheet = ss.getSheetByName('​Match');

var lastColumn = sheet.getLastColumn();

var ​values​ = sheet.getRange(​3​, 1, 1, lastColumn).getValues();

var index = ss2.getSheetByName('​IndexMatch2');

var map = index.getRange(​3​, 1, index.getLastRow(), index.getLastColumn()).getValues();

for (​var ​i = ​0​; i < ​values​.length​-2​; i++) { var name1 = values[i][0];

var name2 = values[i][1];

for (var j = 0; j < map.length - 2; j++) {

var index_name1 = map[j][3];

var index_name2 = map[j][4];

​ var index_value = map​[j][5];

if (name1 && name2 == index_name1 && index_name2) {

var match = index_value

}}}

[Edward Ulle]

Your if is wrong see how I did it.

function test4() {

  try {

    var ss = SpreadsheetApp.getActive();

    var sh = ss.getSheetByName('Match');

    // you only get 1 row

    var values = sh.getRange(3, 1, 1, sh.getLastColumn()).getValues();

    console.log("values = "+values)

    var index = ss.getSheetByName('IndexMatch2');

    // index.getLastRow()-2 otherwise get 2 blank rows

    var map = index.getRange(3, 1, index.getLastRow()-2, index.getLastColumn()).getValues();

    console.log("map = "+map);

    for( var i=0; i<values.length; i++ ) {

      var name1 = values[i][0];

      var name2 = values[i][1];

      for( var j=0; j<map.length; j++ ) {

        var index_name1 = map[j][3];

        var index_name2 = map[j][4];

        if( ( name1 === index_name1 ) && ( name2 === index_name2 ) ) {

          var match = map[j][5];

          console.log("match = "+match);

          // now what do you do with it?

        }

      }

    }

  }

  catch(err) {

    console.log(err);

  }

}

[Laurie Nason]

Hi,

Is there a reason that you are not using Vlookup in the sheet itself? (one might be that you don't need the column the result ends up in to be dynamic)

If dynamic is an option, I would create a helper column on sheet 1 which concatenates the values in A&B = e.g. use Arrayformula at the top of the column and do something in cell C2 like "=ARRAYFORMULA(IF(A2:A="","",A2:A&B2:B))"

Then you do the same on your Sheet 2 - with another column (that you can hide if it will confuse users) to combine (cols D &E) 

Finally you can do a simple VLOOKUP on Sheet 1 to return the value from Column F based on the two columns you just created. I do this a lot where my unique values are combinations of columns (I tend to put the column way over to the right of the sheet in Columns X,Y,Z or somewhere out of sight) - Another nice trick(?) is that when you do the range part of VLOOKUP - you can use something like {SHEET2!HelperCol,SHEET2!ValueCol} and return column 2 with the order of the columns different to the actual order across the sheet.

If you still want to do this in a script, then I think your final 'if' statement might be the problem, however, if you have a lot of rows in your sheet that this will run on, you will start to have performance issues as the recalculation has to happen for every row you put the formula in. It's usually best in the case of updating a column to run the script once to update all the values (you can check for blanks so you don't have to do run it for already entered results).

Hope this helps,

Laurie 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ec1690b2-fe56-4a79-aa52-d888ebe35821n%40googlegroups.com.

[Chris Kirkwood]

Laurie is also correct if you wanted to fix this to be more dynamic and the if statement logic is not correct.

I created a spreadsheet that had two separate sheets (as I assumed your SS has) and did not have the sheet name as a header in the first row so the getRange starts at row 2, not 3 as in your example.

Notes:

- ss2 was not defined and not needed

- type Range in class Sheet indexes starting at 1,1; when accessing an array, indexes begin at 0,0;

- for loops in code are to show the indexing and output - not germane to the execution 

- getLastRow -1 necessary due to column header row 

- if statement logic is wrong - name1 and name 2 will always be false hence the if statement will return false

- all console.log messages are to see what is going on 

Took as stab using your semantics/syntax

function getValue(){

ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("Match");

var lastColumn = sheet.getLastColumn();

var values = sheet.getRange(2, 1, 1, lastColumn).getValues();

var index = ss.getSheetByName("IndexMatch2");

var map = index.getRange(2, 1, index.getLastRow()-1, index.getLastColumn()).getValues();

// * DEBUG* for loops to console.log each element of the array map to highlight indexing

console.log('Array values::',values);

console.log('Array map', map);

for (i = 0; i < index.getLastRow()-1; i++){ // -1 to account for header row

for (j = 0; j < index.getLastColumn(); j++){

console.log('map[',i,'][',j,']= ',map[i][j]);

}

}

// end of loop

for (var i = 0; i < values.length; i++) {

var name1 = values[i][0];

var name2 = values[i][1];

for (var j = 0; j < map.length; j++) {

var index_name1 = map[j][0];

var index_name2 = map[j][1];

var index_value = map[j][2];

console.log('index1',index_name1);

console.log('index2',index_name2);

console.log('value',index_value);

//if (name1 && name2 == index_name1 && index_name2)

if ((name1 == index_name1) && (name2 == index_name2)){

var match = index_value;

console.log('Match!',match);

}

}

}

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BA7ZWL2Ek9ESX6AGwMrVkAMT01N09J5qxgeqVFpcFkJYoSJGA%40mail.gmail.com.

--

Chris Kirkwood 
There are 10 types of people in this world…. those that know binary, and those that don’t.

[JMR...]

hi -

thew...@'s suggestion worked. If I am not running any script, then using a formula would work just fine. However, I need the script to automate one of the tasks I am doing and there is no reason for me to keep the value in the sheet.

Thank you so much for all the suggestions. I appreciate it.