Is there a way to do an Index Function array across multiple sheets using named ranges (all names the same on each sheet, but scoped to that sheet)? For example, something like
={INDEX('Sheet1:Sheet30'!rngTable,MATCH(A2,'Sheet1:Sheet30'!rngRow,0),MATCH(B1,'Sheet1:Sheet30'!rngColumn,0))} with A2 being the row value and B1 being the column value you want to return from the index on each sheet (rngTable is the table array, rngRow is the row of that table, and rngColumn is the column of that table for each sheet). I can repeat the equation 30 times for each sheet to get the same result, but that's kind of a pain and a nasty long formula. The above doesn't work, so was wondering if there is an alternative solution that actually does work. Thanks!