Forum Discussion
Index match with multiple sheets
Attempting to apply the same method that Sergei used, I believe the attached file is what you are trying to do? Since he is using structured tables (naming the 3 tables DamA, DamB, DamC), he can reference them by table name and column name, using the indirect function, instead of referencing the sheet they are on (format is "Table[Field]").
For this one, index/match would be appropriate to pull the specific year (row) from the table.
Since the master sheet presents the data in the same order as the individual data sheets, it's not really necessary to match the columns, so it's returning all of the columns on the row to which it matched (as Sergei did also, if I remember correctly).
The INDEX function is capable of returning all rows and/or all columns of whatever row/column it matches to. This option is selected by inputting a "0" in either the row or column argument.
=INDEX(MATCH(), 0) > returns all columns of the row to which it matches.
=INDEX(0, MATCH()) > returns all rows of the column to which it matches.
Since the formula is returning multiple values, you have to select a range that is the same dimension as the table that the formula will return (B6:N6) when you enter the formula (though newer versions of Excel with dynamic arrays activated should not require this I think).
If your months on the master sheet were in a different order, or were only for certain months, then you could match the month to the table header rows in the data sheets by replacing the second argument of the INDEX function (0) with another match function:
=IFERROR(INDEX(INDIRECT($H$3),MATCH($J$3,INDIRECT($H$3&"["&$B$5&"]"),0),MATCH(B$5,INDIRECT($H$3&"[#Headers]"),0)),0)