I've been looking around for an example where I could create a custom function where I'd be able to put two values in and look the cell value from another sheet. For example in the screenshot sheet "monthTest" we'd input "Credit Card Fees" and "1/1/21", it would then look up on sheet sumActuals and return 360 because "Credit Card Fees" in column A matched the input "Credit Card Fees" and "1/1/21" match "1/1/21" from row 1 in the sumActuals sheet.

Sheet where custom function is entered

Sheet where custom function looks up values

https://docs.google.com/spreadsheets/d/17SId7mIzO3hVOC36Nq40O0bjPS5YfGOX4wsMU1NlbCU/edit?usp=sharing

CodePudding user response：

function lookup(col1, header) {
  if (col1 && header) {
    const ss = SpreadsheetApp.getActive();
    const sh = ss.getSheetByName('monthTest');
    const [hA, ...vs] = sh.getDataRange().getValues();
    let col = {};
    hA.forEach((h, i) => col[h] = i   1);
    for (let i = 0; i < vs.length; i  ) {
      if (vs[i][0] == col1) {
        //Logger.log(sh.getRange(i   2, col[header]).getValue());
        return sh.getRange(i   2, col[header]).getValue();
      }
    }
  }
}