2

Not sure if this is possible in Google sheets, but I'd like to search my named ranges for a value and return the name of the range where the value exists. If I have ranges "Alpha" and "Beta", and within Alpha is the value "first", when I type "first" into my cell I want the formula will spit out "Alpha". Can it be done? I prefer a formula so that I can include it in other formulae.

Doubt
  • 1,163
  • 2
  • 15
  • 26
user15250594
  • 111
  • 5

2 Answers2

1

You might need to create a custom function to get this result. To create a custom function follow the instructions here, and then paste the following code into code.gs:

function WHATRANGE(col, row) {

  var result = "Null";
  var rangeList = SpreadsheetApp.getActive().getNamedRanges();

  rangeList.forEach(function (namedRange) {

    var range = namedRange.getRange();
    
    if (col >= range.getColumn() && col <= range.getLastColumn()
    && row >= range.getRow() && row <= range.getLastRow()) {

      result = namedRange.getName();

      }

  });

  return result;
  
}

This function cycles through the named ranges, and determines if the arguments row and col are within that range. If they are, it outputs the name of the range; if it is not, it outputs Null. To call this function in Google Sheets, enter =WHATRANGE(3,4).

I will assume your data is in one column, namely column A (column 1). Then use:

=WHATRANGE(1,MATCH("first",A:A,0))
Doubt
  • 1,163
  • 2
  • 15
  • 26
1

As another approach, from your question, I guessed the following 2 patterns.

Pattern 1:

If you have already known the names of all named ranges like Alpha and Beta, how about the following formula? In this case, the cell "A1" has a value of "first". If the inputted value is not included in the named ranges, "Not found." is returned.

=LET(
  alpha,COUNTIF(Alpha,"="&A1)>0,
  beta,COUNTIF(Beta,"="&A1)>0,
  IFS(AND(alpha,beta),"Alpha,Beta",alpha,"Alpha",beta,"Beta",TRUE,"Not found.")
)

Pattern 2:

If you have not known the names of all named ranges, how about the following custom function? In this case, please copy and paste the following script to the script editor of Spreadsheet and save the script. When you use this script, please put a custom function of =SAMPLE("first"). By this, the name of the named range is returned. If the inputted value is not included in the named ranges, "Not found." is returned.

const SAMPLE = search =>
  SpreadsheetApp.getActiveSheet().getNamedRanges().reduce((ar, r) => {
    if (r.getRange().createTextFinder(search).matchEntireCell(true).findNext()) {
      ar.push(r.getName());
    }
    return ar;
  }, []).join(",") || "Not found.";

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • So I tried the =LET method and it works well. However, the downside is that I have a lot of named ranges, which would be a bit tedious to input. Also, if I ever add a range in the future the list of ranges would not be dynamic, since each range needs it's own line. Still, I do like it as a formulaic solution. I intend to try the script options tomorrow when I'm less tired, and will report back. :) – user15250594 Jun 13 '23 at 05:28
  • @user15250594 Thank you for replying. If my answer was not useful, I apologize. – Tanaike Jun 13 '23 at 06:12