Home > Enterprise >  Search an entire google sheet, return a value from matching cell relative to the position of the mat
Search an entire google sheet, return a value from matching cell relative to the position of the mat

Time:01-07

I had originally asked the following question enter image description here

It should look like this:

=ARRAYFORMULA(QUERY(MAP(BYROW(AY2:AY31,LAMBDA(day,
SPLIT(CONCATENATE(MAP(November!A2:Z,LAMBDA(sheet,
IF(sheet=day,JOIN("❄️",BYROW("November!"&ADDRESS(
ROW(sheet) {1,0;2,0},COLUMN(sheet) {0,3}),
LAMBDA(row,JOIN("❄️",row)))),)))),"❄️"))),
LAMBDA(cell,INDIRECT(cell))),"select Col1, Col2, Col3"))

The same applies for February: change F2:F32 to F2:F28

=ARRAYFORMULA(QUERY(MAP(BYROW(F2:F28,LAMBDA(day,
SPLIT(CONCATENATE(MAP(February!A2:Z,LAMBDA(sheet,
IF(sheet=day,JOIN("❄️",BYROW("February!"&ADDRESS(
ROW(sheet) {1,0;2,0},COLUMN(sheet) {0,3}),
LAMBDA(row,JOIN("❄️",row)))),)))),"❄️"))),
LAMBDA(cell,INDIRECT(cell))),"select Col1, Col2, Col3"))

Output

From:

enter image description here

To:

enter image description here

  • Related