I would like the function function(A:A,"Andy") to return something like B2, B4, B6:B8 Basically, all the cells containing the value Andy in the range I've already specified. I've looked in many places and didn't find anything that exactly does this.

CodePudding user response：

Try the following

=INDEX(FILTER(A2:B,A2:A="Andy"),,2)

OR
you may need this result

=INDEX(ADDRESS(FILTER(A2:B,A2:A="Andy"),2,4),,2)

CodePudding user response：

Try this:

function xfind(x=5) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues().map((r,i)=>{
    let row = [];
    r.forEach((c,j) => {
      if(c == x) {
        row.push(sh.getRange(i 1,j 1).getA1Notation())
      }
    });
    return row
  }).flat().join(',');
  Logger.log(vs)
  return vs;
}

I ran it on a spreadsheet like this:

=xfind(7) and it returned the string L2,O3,P3,O4,D6,J6,T6,D7,L7,B8,D8,K9,H12,O12,C13,K13,E14,J14,E15,H19,R20

I made no attempt to join consecutive cells into larger ranges.