Google Sheets - Simple VLOOKUP with Double Drop Down Data Validation

Here is a script solution:

Script:

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  const column = r.getColumn();
  const row = r.getRow();
  // if non header in 'Pending' sheet is edited and the outcome has a value  
  if (src.getSheetName() == 'Pending' && row > 1 && e.value) {
    // get data as a whole
    var data = e.source.getSheetByName('Profiles').getDataRange().getValues();
    // if A2:A is edited
    if (column == 1) { 
      // filter 1st column using e.value, then return 2nd column
      var emails = data.filter(row => row[0] == e.value).map(row => row[1]);
      // set data (list) validation to colB
      r.offset(0, 1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(emails).build());
      // delete data in colB and colC
      src.getRange(row, column + 1, 1, 2).clearContent();
    }
    // if B2:B is edited
    else if (column == 2) {
      var colA = r.offset(0, -1).getValue();
      var colB = e.value;
      // only populate column C if both A and B has values
      if (colA && colB) {
        var name = data.filter(row => row[0] == colA && row[1] == colB)[0][2];
        // set value to colC
        r.offset(0, 1).setValue(name);
      }
    }
  }
}