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.
As another approach, from your question, I guessed the following 2 patterns.
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.")
)
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.";