Compare two columns and if match, paste matching value

Question

This seems like it should be super simple but I really cannot find a solution via Google Script. I want to compare all the cells in SKU (A) in Import to SKU (A) in Pricelist, and if there is a match then paste the matching SKU's into Sheet 3 (A).

Normally I would just do this through index match but I need to do it with Google Script.

I have tried the following and this works for checking a single cell (A81) with the whole range in Pricelist A1:A100 but I cannot make sense of it when checking a range against a range.

function checkProduct() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lookup = ss.getRange('Import!A81').getValue();
  var range = ss.getRange('Pricelist!A1:A200').getValues();
  var lookupRange = [];

  for (var i = 0; i < range.length; i++) {
    lookupRange.push(range[i][0]);
  }
  var index = lookupRange.indexOf(lookup);
  if (index == -1) {
  }
  else {
    ss.getRange('Sheet3!A1').setValue('its there'); // need to paste in the matching SKU
  }
}

Import Sheet

SKU | Price
s123 | 99
s124 | 98
s125 | 97

Pricelist Sheet

SKU | Price
s123 | 99
a111 | 98
a453 | 97

Ed Nelson · Accepted Answer · 2017-02-06 14:09:28Z

3

Try this:

function checkProduct() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getSheetByName("Import")
  var lr=s.getLastRow()
  var lookup = s.getRange(2,1,lr-1,2).getValues();
  var s1=ss.getSheetByName("Pricelist") 
  var lr1=s1.getLastRow()
  var range = s1.getRange(2,1,lr1-1,2).getValues();
  var s3=ss.getSheetByName("Sheet3")
  var lookupRange = [];
  for (var i = 0; i < lookup.length; i++) {
     for (var j = 0; j < range.length; j++) {
     var  test=lookup[i][0]
         if(lookup[i][0]==range[j][0]){
           lookupRange.push([range[j][0],range[j][1],lookup[i][0],lookup[i][1],]);
     }}}
   s3.getRange(2,1,lookupRange.length,4).setValues(lookupRange); 
}

I am pushing all 4 values on a match. Adjust as needed.

edited Feb 6, 2017 at 14:09

answered Feb 4, 2017 at 23:41

Ed Nelson

10.4k2 gold badges32 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oli Girling Over a year ago

Thanks very much for the reply. This makes sense, but getting this error - TypeError: Cannot read property "0" from undefined. (line 29). This line is if(lookup[i][0]==range[j][0]){

Collectives™ on Stack Overflow

Compare two columns and if match, paste matching value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related