INDEX/MATCH with dynamic sheet name

[gb1993]

Hello,

I am trying to set up a formula that will execute an INDEX/MATCH in one of a variety of sheets, depending on cell being matched. I believe the INDIRECT function is part of the solution but I haven't had any luck with it. I am happy to use a number of distinct steps as working formulae.

As background info:

• The sheet names in question are called "Exhibit-A", "Exhibit-B", "Exhibit-C" etc.
• The column headings (A, B, C etc) are in columns D6:N6

The whole process essentially needs to (using row 10 as an example)

1. Return the column heading of the maximum value in a row into a new column (Column O) (I have successfully used here [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDEX($D$6:$N$6, MATCH(MAX(D10:N10), D10:N10, 0)[/FONT]
2. [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Concatenate this to create the name of the sheet (I have successfully used here =CONCATENATE("Exhibit-",O10)[/FONT]
3. [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Perform an INDEX/MATCH in that sheet along the following lines[/FONT]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]

• [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]​[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]INDEX('sheet_name'!$F$5:$F$800, MATCH('a value in Column P', 'sheet_name'!$E$5:$E$800, 0)[/FONT][/FONT]<strike></strike>

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT][/FONT]
Thanks - please let me know if you require any more info.
<strike></strike><strike></strike>

 

[steve the fish]

Hi. A little long but try this:

=INDEX(INDIRECT("'Exhibit-"&INDEX($D$6:$N$6,MATCH(MAX(D10:N10),D10:N10,0))&"'!F5:F800"),MATCH(P1,INDIRECT("'Exhibit-"&INDEX($D$6:$N$6,MATCH(MAX(D10:N10),D10:N10,0))&"'!E5:E800"),0))

You could split the index match to find the letter into another cell if you like and just refer to that cell.

This in F1:

=INDEX($D$6:$N$6,MATCH(MAX(D10:N10),D10:N10,0))

then use:

=INDEX(INDIRECT("'Exhibit-"&$F$1&"'!F5:F800"),MATCH(P1,INDIRECT("'Exhibit-"&$F$1&"'!E5:E800"),0))

 

[gb1993]

Thanks Steve - that's worked perfectly.