I have a google sheet that's laid out like a wall calendar.
I'd like to take data from two cells relative to each calendar date in this calendar view and add them to a single row in another sheet.
I've added notes with expected results in the "entries" sheet here: https://docs.google.com/spreadsheets/d/1eTtmNR-9dbWG_bDhzpWgza5idUphodmJsI6sS0DU0og/edit?usp=sharing
I explored vlookup and index match, but they didn't do the trick. I had a hard time searchign both rows and columns, matching the dates, and based on that match, selecting the relative cell.
Try this out in Entries!B2
=ARRAYFORMULA(MAP(BYROW(A2:A32,LAMBDA(day,
SPLIT(CONCATENATE(MAP(December!A2:Z,LAMBDA(sheet,
IF(sheet=day,JOIN("❄️","December!"&ADDRESS(
ROW(sheet)+{1,0},COLUMN(sheet)+{0,3})),)))),"❄️"))),
LAMBDA(cell,INDIRECT(cell))))
This formula will spill in the range B2:C32 so make sure that it's empty.
UPDATE:
=ARRAYFORMULA(QUERY(MAP(BYROW(A2:A32,LAMBDA(day,
SPLIT(CONCATENATE(MAP(December!A2:Z,LAMBDA(sheet,
IF(sheet=day,JOIN("❄️",BYROW("December!"&ADDRESS(
ROW(sheet)+{1,0;2,0},COLUMN(sheet)+{0,3}),
LAMBDA(row,JOIN("❄️",row)))),)))),"❄️"))),
LAMBDA(cell,INDIRECT(cell))),"select Col1, Col2, Col3"))