https://www.pbainbridge.co.uk/2018/12/lookup-email-address.html

Lookup Email Address.gs

function lookupEmailAddress() {
  
  // get 'Plan' sheet
  var planSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Plan');
  // get 'Plan' sheet data range
  var planDataRange = planSheet.getDataRange();
  // get 'Plan' sheet data values
  var planData = planDataRange.getValues();
  // get number of last row of data in 'Plan' sheet
  var planLastRow = planSheet.getLastRow();
  
  
  // get 'Email Address' sheet
  var emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email Address');
  // get 'Email Address' sheet data
  var emailData = emailSheet.getDataRange().getValues();
  // get number of last row of data
  var emailLastRow = emailSheet.getLastRow();
  
  
  // loop through each Assignee from 'Plan' sheet to get relevant details (skipping row headers) **********
  for (i=1; i<planLastRow; i++) {
    
    // check Assignee cell is not blank before proceeding
    // i = row, 6 = column - as is 2-D array
    if (planData[i][6]) {
      Logger.log('Assignee cell is not blank');
      
      
      // loop through each Assignee from 'Plan' with each Initials in 'Email Address' to find match ******
      for (j=0; j<emailLastRow; j++) {
        
        if (planData[i][6] == emailData[j][0]) {
          Logger.log(planData[i][6] + ' matches ' + emailData[j][0]);
          // if match found then get corresponding email address from column 2
          var assigneeEAddress = emailData[j][1];
          Logger.log('Email address is: ' + assigneeEAddress);
        } // end of 'if match' loop **********************************************************************
        
      } // end of 'j' loop *******************************************************************************
      
      
    } // end of 'if' for checking Assignee cell not blank
    else {Logger.log('Assignee cell is blank')};
    
  } // end of 'i' loop through each Assignee **************************************************************
  
  
}


function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu('Menu')
  .addItem('Email Assignees', 'lookupEmailAddress') // label for menu item, name of function to run.
  .addToUi();
}