Lookup value in cell if another cell in the row contains a matching value

Question

I have two Google Speadsheet sheets. I want to cross-reference the two sheets, and update the rows in SheetA with the value of cellA in SheetB, if CellC in SheetB contains the value of CellA in SheetA.

SheetA looks like this:

A. Word   | B. Other value | C. Another value
---------------------------------------------
word 1    | 1234           | 5678
word 2    | 3921           | 73643
word 3    | 4020           | 43985
word 4    | 32323          | 32325

SheetB looks like this:

A. Code  | B. Category | C. List of words
-----------------------------------------------
1.1.1    | Cat1a       | word 1, word 5, word 7
1.1.2    | Cat1b       | word 3, word 2, word 9
1.2      | Cat2        | word 5, word 6, word 4
1.2.1    | Cat2a       | word 8, word 10

Now, if cellC, "List of words" contains the word from cellA, "Word" in SheetA, then update that row in SheetA with the value in CellA ("Code") from SheetB.

How can I do that?

I suppose the best way is to create a script that looks up the values in SheetB-CellC to find matches and then update. But although I have done some scripting, I'm usually more comfortable with using formulas directly in the spreadsheet cells. But is there a function that can match the value of one cell with part of value in another cell?

Thanks!

Daniel Westergren Daniel Westergren · Accepted Answer · 2015-08-20T12:42:27

I actually found part of the solution here: https://stackoverflow.com/a/11443715/1688190

What I had to do, however, was to add a check so that only one value for each target row was being output. Otherwise each iteration of the source, trying to look for the value from the target, output another line.

My working code:

function updatecategory() {
/* let us say source array with name(columnA) & ID(columnB) is array 'source'
and target array with only IDs is array 'target', you get these with something like*/
var source = SpreadsheetApp.getActiveSpreadsheet().getSheets()[4].getDataRange().getValues();
// and
  var target = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange("A:A").getValues();// if other columns, change index values in the arrays : 0=A, 1=B ...
//   then let's create a 3 rd array that will be the new target with ID + names, and call it 'newtarget' 
var newtarget=new Array()
// the iteration could be like this :
      for(i=0;i<target.length;++i){ // don't miss any ID
        var found=""
       for(j=0;j<source.length;++j){ // iterate through source to find the name

         if(source[j][2].toString().indexOf(target[i][0].toString())>-1){
           var newtargetrow=[target[i][0], source[j][0]] // if match found, store it with name (idx0) and ID (idx 1)
           //Logger.log(target[i][0].toString().match(source[j][0].toString()) + " " + source[j][0].toString())
           //newtarget.push(newtargetrow);// store result in new array with 2 columns
           found="found"
           Logger.log(found)
           //Logger.log(newtarget)
         } else if (found != "found") {
           var newtargetrow=[target[i][0], 'not found'] // if no match, show it in name column
           //Logger.log(found)
         }

       }
        Logger.log(newtarget)
         newtarget.push(newtargetrow);// store result in new array with 2 columns
       //loop source
       /* now you have a newtarget array that can directly overwrite the old target 
    using setValues() */
    var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];// assuming the target sheet is sheet nr2
    sh.getRange(3,1,newtarget.length,newtarget[0].length).setValues(newtarget);


     } // loop target

    /* now you have a newtarget array that can directly overwrite the old target 
    using setValues() */
    var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];// assuming the target sheet is sheet nr2
    sh.getRange(3,1,newtarget.length,newtarget[0].length).setValues(newtarget);
//

}

Lookup value in cell if another cell in the row contains a matching value

1 Answers