Google Sheets – Reference Tabs with Specific Word in COUNTIF

Better is to prepare a result for a range in stead of dealing with individual cell. When you open the file, it needs to process all the cells individually. This, in the end, evoked the error. Therefore I created the below code.

Code

function catExp_1(range) {
  var output = [], arr = JSON.parse(createArr());
  for(var j=0, jLen=range.length; j<jLen; j++) {
    var result = "Not Sure";
    for(var i=0, iLen=arr.length; i<iLen; i++) {      
      for(var k=0, kLen=arr[i][1].length; k<kLen; k++) {        
        if(range[j][0].indexOf(arr[i][1][k]) != -1) {
          result = arr[i][0];
        }
      }      
    }
    output.push([result]);
  }
  return output;
}

function createArr() {
  // check for availability
  var cache = CacheService.getPublicCache();
  var cached = cache.get("accounts");
  if (cached != null) {  
    return cached;
  }

  // create array
  var arr = [    
    [["5070 - Bank Charges"],["PURCHASE INTEREST", "xxxx"]],
    [["5530 - Postage Expense"],["CPC SCP","xxxx"]],
    [["9999 - xxxxxxx"],["xxxxxxxx"]]
  ];

  // add to cache
  var jsonData = JSON.stringify(arr);
  cache.put("accounts", jsonData, 21000);
  return jsonData;
}

Explained

The first script will create an output array and collect the lookup data. This is either stored in the cache or prepared on the fly (and stored in the cache for the next time). The iteration start with a category Not Sure. If an match is found, then the corresponding value will be push into the output array. Else the Not Sure will be added.

Usage

=catExp_1(B2:B133)

Note

You might want to consider adding the categories, rather than looking them up. This eliminates any delay from the script. You can easily create a menu item that does this.

No, for the same reason that one can't shave one's reflection in a mirror.

Spreadsheets data links are one-way, with a source and a target. The source range can be edited, the target range cannot be, because its job is to reflect the data as it is present in the source.

As pnuts commented, copying and pasting back values only (e.g., Ctrl-C followed by Ctrl-Shift-V) is a way to break the link of the target range with the source. Then the target will be editable, since it is no longer a reflection of the source. Consequently, it will no longer be updated if the source changes.