Cross reference between 2 sheets of a Google Sheet

Question

I have a Google Spreadsheet that has 2 sheets, People & Companies:

If a cell in Column B of People matches the text in a cell of Column A in Companies then I need the contents of Column B in Companies added to Column C in People

PEOPLE SHEET BEFORE :

COMPANY SHEET :

PEOPLE SHEET AFTER :

I've been attempting to use a matching script found elsewhere on Stackoverflow but it's been unsuccessful:

 function myFunction(){

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var people = sheet.getSheetByName("People");
  var companies = sheet.getSheetByName("Companies");

  var people = people.getRange(column(b));
  var data1 = people.getValues();

  var data2 = companies.getValues();

}

I'm really stuck, any idea? Thank you in advance for any help.

Answer 1

You don't need script. Vlookup will do it. Enter this in 'People' C2:

=iferror(arrayformula(vlookup(B2:B,Company!A2:B,2,false)),"")

Answer 2

Although Vlookup will do it for you, this is one way to do it by script if you're still curious.

function myFunction(){
  var peopleSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PEOPLE");
  var companySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("COMPANIES");

  // --------- SPECIFY INFO --------------
  var ignoreTitles = true; // If you have titles, this will offset them.

  var peopleColumn = 2; // Which column to use in the People sheet to check comparison
  var companyColumn = 1; // Which column to use in the Company sheet to check comparison

  var companyColumnToGet = 2; // Which column in Company sheet to transfer data from
  var peopleColumnToSet = 3; // Which column in People sheet to transfer data into
  // -------------------------------------

  var start = 1;
  if(ignoreTitles) {
    start++;
  }

  for(var i=start; i<=peopleSheet.getDataRange().getValues().length; i++) {
    for(var j=start; j<=companySheet.getDataRange().getValues().length; j++) {
      if(peopleSheet.getRange(i,peopleColumn).getValue() === companySheet.getRange(j,companyColumn).getValue()) {
        peopleSheet.getRange(i,peopleColumnToSet).setValue(companySheet.getRange(j,companyColumnToGet).getValue());
      }
    }
  }
}