Search a catalog for matching values and getting a 3rd column as a result

Question

I have 2 columns A and B that contain Country and State/City. This is a spreadsheet called Data, I have another spreadsheet called Catalog that contains the Country and City along side different data on the following columns. I want to search the combination of both A column and B column and obtain C value of Catalog

I could have data like:

A    |       B       |     C     |   D
USA  | Washington DC |   23423   |   DC
USA  |    Maryland   |   23421   |   MD

I want to get C or D from the catalog spreadsheet

In the data catalog I would have:

A    |       B       |     C    
USA  | Washington DC |  Run Function(A1:B1, Catalog!A2:B222)
USA  |    Maryland   |  Run Function(A2:B2, Catalog!A2:B222)

Using Bard, I've gotten this function:

function searchCatalog(rangeOfData, rangeOfCatalog) {
  const spreadsheet = SpreadsheetApp.getActive();
  // Get the values in the range of data.
  var dataValues = spreadsheet.getRange(rangeOfData).getValues();
  // Get the values in the range of catalog.
  var catalogValues = spreadsheet.getRange(rangeOfCatalog).getValues();
  // Iterate over the rows in the catalog.
  for (var i = 0; i < catalogValues.length; i++) {
    // If the data matches the catalog, return the code.
    if (dataValues[0] == catalogValues[i][0] && dataValues[1] == catalogValues[i][1]) {
      return catalogValues[i][2];
    }
  }
  // If the data does not match the catalog, return None.
  return 'None';
}

I keep getting Range not found

Answer 1

Modification points:

• In your showing script and your 2 tables and your question, if Run Function(A1:B1, Catalog!A2:B222) is searchCatalog(A1:B1, Catalog!A2:B222), the values of A1:B1 and Catalog!A2:B222 are given as the 2-dimensional arrays. But, your script uses them as the range. I guessed that this might be the reason for your current issue of I keep getting Range not found.

• And, in your expected result, you want to retrieve the value of column "C" of "Catalog" sheet. But, your custom function uses only columns "A" and "B". I think that this might be your 2nd issue.

When these points are reflected in your script, how about the following sample script?

Sample script:

function searchCatalog(rangeOfData, rangeOfCatalog) {
  const values = rangeOfCatalog.find(([a, b]) => [a, b].join("") == rangeOfData[0].join(""));
  return values ? values[2] : "None";
}

• In this case, please put a custom function of =searchCatalog(A1:B1, Catalog!A2:C222) into a cell. By this, this script is run and the value is returned. When your showing 2 tables are used, =searchCatalog(A1:B1, Catalog!A2:C222) returns 23423.

• If you want to use your showing script, in this case, it is required to give the vlues as the string like =searchCatalog("A1:B1", "Catalog!A2:C222"). Please modify it as follows.

  function searchCatalog(rangeOfData, rangeOfCatalog) {
    const spreadsheet = SpreadsheetApp.getActive();
    // Get the values in the range of data.
    var dataValues = spreadsheet.getRange(rangeOfData).getValues()[0]; // Modified
    // Get the values in the range of catalog.
    var catalogValues = spreadsheet.getRange(rangeOfCatalog).getValues();
    // Iterate over the rows in the catalog.
    for (var i = 0; i < catalogValues.length; i++) {
      // If the data matches the catalog, return the code.
      if (dataValues[0] == catalogValues[i][0] && dataValues[1] == catalogValues[i][1]) {
        return catalogValues[i][2];
      }
    }
    // If the data does not match the catalog, return None.
    return 'None';
  }
  

• As another pattern, when you want to retrieve the result values using one custom function, how about the following sample script? In this case, please put a custom function of =searchCatalog(A1:B, Catalog!A2:C222) into a cell. By this, the script is run.

  function searchCatalog(rangeOfData, rangeOfCatalog) {
    const obj = rangeOfData.map(r => r.join(""));
    return rangeOfCatalog.map(([a, b, c]) => obj.includes([a, b].join("")) ? c : "None");
  }
  

Reference:

• Custom Functions in Google Sheets