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

Answer 2

Try it this way:

function searchCatalog(rangeOfData = "Sheet0!A2:P22", rangeOfCatalog = "Sheet1!A2:P22") {
  const ss = SpreadsheetApp.getActive();
  var vs = ss.getRange(rangeOfData).getValues();
  var cs = ss.getRange(rangeOfCatalog).getValues();
  var o = vs.map((r, i) => {
    if (r[0] == cs[i][0] && r[1] == cs[i][1]) {
      return [cs[i][2]];
    }
  }).filter(e => e);
  if(o && o.length > 0) {
    Logger.log(JSON.stringify(o))
    return o;
  }
}

Sheets 0 and 1 both has same data to guarantee output

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10	COL11	COL12	COL13	COL14	COL15	COL16
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
3	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17
4	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
5	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
6	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
7	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21
8	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22
9	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
10	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
11	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25
12	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26
13	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
14	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
15	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29
16	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
17	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
18	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32
19	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33
20	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34
21	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35
22	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36

The output is the third column

[[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]]