INDIRECT function together with INDEX MATCH

Hi,

I have a formula in an existing project

=INDEX('Table 9'!B11:L107,MATCH(intResult!D12,'Table 9'!A11:A107,0),MATCH(intResult!C25,'Table 9'!B10:L10,0))

This works fine for extracting the cell data from the array in Sheet!Table 9 the issue is there are 28 tables on 28 different sheets. The sheet names are 'Table 1', 'Table 2' and so on. At the moment I am having to duplicate the pair of sheets intResult and the Table sheet 28 times which is so wasteful

I have a cell D5 on my intResult sheets which contains the correct table to be used (and so sheet name)

I have seen mention of INDIRECT function that can pass the sheet name to the formula but I am unsure of the correct syntax and all efforts on my part so far result in REF#. If I could get this working I would only be in need of one intResult sheet and the Table sheets.

The link also on this forum offering a soluction to a previous question is http://www.excelforum.com/excel-gene...ect-match.html

Any help offered here would be most appreciated.

Try this

=INDEX(INDIRECT("'"&intResult!D5&"'!B11:L107"),MATCH(intResult!D12,INDIRECT("'"&intResult!D5&"'!A11:A107"),0),MATCH(intResult!C25,INDIRECT("'"&intResult!D5&"'!B10:L10"),0))

So if the sheetname, Table 9 is in D5, the...

=INDEX(INDIRECT("'"&intResult!D5&"'!B11:L107"),MATCH(intResult!D12,INDIRECT("'"&intResult!D5&"'!A11:A107"),0),MATCH(intResult!C25,INDIRECT("'"&intResult!D5&"'!B10:L10"),0))

although if the active sheet is intResult, you don't need to include that in the formula.