Google-sheets – Trying to search(from sheet1 ) for a value on in any row of sheet2

You want to enter an employee name, search for that name on another sheet, and return the information for that employee to the initial sheet.

Search Name - Data validation

I simplified the search (and also reduced the risk of error) by using data validation in cell I20 of "Data Storage" - the parameters are "List from a Range", and the range is "Main!C2:C". This range enables you do add more employees to the "Main" sheet and those employee names will automatically appear in the drop down list on "Data Storage".

Processing

• getDataRange() Return the entire data range for "Main" at the outset.
• Column C of Sheet "Main" is searched using Javascript indexOf.
  • Column C = var employeedata = datavalues.map(function(e){return e[2];});
  • Search = var searchresult = employeedata.indexOf(searchname);
• Error checking: indexOf returns a value of -1 if the search term is not present. I added an Alert to provide user feedback, but if Data validation is used, then the likelihood of an error is slim.
• indexOf provides the basis for selecting the row number of "Main" so that the relevant employee data can be returned
  • var searchdata = data.getRange(searchresult+1, 1, 1, datalastLC).getValues();
• the data is returned as a row. In my example, I transposed that row to be able to create vertical output.
  • var searcdatavertical = searchdata[0].map(function(elem) {return [elem];});

function wa14849101() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  // set up the sheet names
  var datasheetname = "Main"; 
  var searchsheetname = "Data Storage";
  var data = ss.getSheetByName(datasheetname);
  var search = ss.getSheetByName(searchsheetname);

  // get the employee name to search
  var searchname = search.getRange("I20").getValue();
  // Logger.log("DEBUG: search name = "+searchname);

  // get the data on data sheet
  var datalastLC = data.getLastColumn();
  var datavalues = data.getDataRange().getValues();
  // Logger.log(datavalues); // DEBUG

  // get the employee name column to search
  var employeedata = datavalues.map(function(e){return e[2];});//[[e],[e],[e]]=>[e,e,e]
  // Logger.log(employeedata); // DEBUG

  var searchresult = employeedata.indexOf(searchname);
  Logger.log("DEBUG: the index number = "+searchresult+" which means that the row number  = "+searchresult+1); 
  
  if (searchresult == -1){
    // indexOf returns -1 if the element is not present
    // Display a dialog box with a title, message, and "OK" button. 
    var ui = SpreadsheetApp.getUi();
    var response = ui.alert('Employee Search', 'Emplopyee'+searchname+' could not be found on the Data sheet\nProcessing stopped.', ui.ButtonSet.OK);
    return;
  }
  
  // get the employee data
  var searchdata = data.getRange(searchresult+1, 1, 1, datalastLC).getValues();
  // Logger.log(searchdata); //DEBUG
  
  // transpose the row array to a column format
  var searcdatavertical = searchdata[0].map(function(elem) {return [elem];});
  // Logger.log(searcdatavertical); DEBUG
  
   // create the output range
  var outputrange =  search.getRange(1, 11, datalastLC, 1); // column K=11, num of rows = number of data columns
  outputrange.setValues(searcdatavertical);

return;
}

Sample Data

Sample search and output

References

map

indexOf