"double" INDEX & MATCH search?

[jxb]

to all

I am trying to do a "double" search with Index & MATCH
I want to return the value in the header (row 7) corresponding to the max value - See attached test
I find the max value - cell c2
and find the ID corresponding to the max value (cell c3) using a match
I know would like to return the case (row 7) corresponding to the max value & ID - in the test v5

This is a test but I'd any (row) range dynamic. Is this possible with INDIRECT,INDEX & MATCH?

How can this be done?

Thanks in advance

 

[jxb]

the proposed solutions (great ones) are combining functions which I am trying to understand
Eric's formula works on the max value whereas I'd like to work on the ID. The reasons as pointed out is that it's possible that one can end up with a number of max values being equal (the same) but ID will always be unique
Just did a test where the max values are the same (different case) for both sets and indeed the 2nd set returns the wrong case

Book1
	B	C	D	E	F	G	H	I
2	max1=	1
3	ID1 =	1
4	case =	v1	v1
5	max2=	1
6	ID1 =	102
7	case =	#REF!	v1	<--wrong
8
9
10	ID	max		v1	v2	v3	v4	v5
11	1	1		1.000	0.357	0.909	0.813	0.650
12	2	0.885865		0.200	0.886	0.472	0.324	0.100
13	3	0.8797		0.087	0.164	0.880	0.014	0.491
14	78	0.933328		0.558	0.660	0.818	0.797	0.933
15	99	0.918975		0.192	0.513	0.910	0.201	0.919
16	101	0.98562		0.986	0.637	0.394	0.041	0.774
17	102	1		0.500	0.216	0.154	0.688	1.000
18	103	0.994172		0.235	0.796	0.550	0.994	0.234
19	104	0.950287		0.613	0.480	0.556	0.950	0.056
Sheet2

Cell Formulas
Range	Formula
D4	=INDEX(headerrow,MATCH(C2,INDEX(WholeDataSet,MATCH(C2,C11:C15,0),0),0))
D7	=INDEX(headerrow,MATCH(C5,INDEX(WholeDataSet,MATCH(C5,C11:C19,0),0),0))
C2	=MAX(C11:C15)
C3	=INDEX(B11:B15,MATCH(C2,C11:C15,0))
C4	=INDEX(headerrow,SUMPRODUCT((E11:I15=MAX(E11:I15))*COLUMN(E11:I15))-COLUMN(E11:I15)+1)
C5	=MAX(C16:C19)
C6	=INDEX(B16:B19,MATCH(C5,C16:C19,0))
C7	=INDEX(headerrow,SUMPRODUCT((WholeDataSet=MAX(WholeDataSet))*COLUMN(WholeDataSet))-COLUMN(WholeDataSet)+1)
C11	=MAX(E11:I11)
C12	=MAX(E12:I12)
C13	=MAX(E13:I13)
C14	=MAX(E14:I14)
C15	=MAX(E15:I15)
C16	=MAX(E16:I16)
C17	=MAX(E17:I17)
C18	=MAX(E18:I18)
C19	=MAX(E19:I19)

Named Ranges
Name	Refers To	Cells
headerrow	=Sheet2!$E$10:$I$10
WholeDataSet	=Sheet2!$E$11:$I$19

 

[Fluff]

If you want to use the ID to specify the row use this version of Eric's formula
=INDEX(E7:I7,MATCH(C2,INDEX(E8:I12,MATCH(C3,B8:B12,0),0),0))

 

[jxb]

Thanks. Looks like it's working. set up a test with all the names (easier to read) see below and will go away and work on my real data set (which is very large)

Book1
	B	C	D	E	F	G	H	I
2	max1=	1.2
3	ID1 =	78
4	case for max1=	v3
5	max2=	1
6	ID2 =	102
7	case for max2=	v5
8
9
10	ID	max		v1	v2	v3	v4	v5
11	1	1		1.000	0.357	0.909	0.813	0.650
12	2	0.885865		0.200	0.886	0.472	0.324	0.100
13	3	0.8797		0.087	0.164	0.880	0.450	0.491
14	78	1.2		0.600	0.660	1.200	0.797	0.933
15	99	0.918975		0.192	0.513	0.910	0.201	0.919
16	101	0.98562		0.986	0.637	0.394	0.041	0.774
17	102	1		0.500	0.216	0.154	0.688	1.000
18	103	0.994172		0.235	0.796	0.550	0.994	0.234
19	104	0.950287		0.613	0.480	0.556	0.950	0.056
Sheet2

Cell Formulas
Range	Formula
C2	=MAX(C11:C15)
C3	=INDEX(B11:B15,MATCH(C2,C11:C15,0))
C4	=INDEX(headerrow,MATCH(_Max1,INDEX(WholeDataSet,MATCH(_ID1,IDRg,0),0),0))
C5	=MAX(C16:C19)
C6	=INDEX(B16:B19,MATCH(C5,C16:C19,0))
C7	=INDEX(headerrow,MATCH(_Max2,INDEX(WholeDataSet,MATCH(_ID2,IDRg,0),0),0))
C11	=MAX(E11:I11)
C12	=MAX(E12:I12)
C13	=MAX(E13:I13)
C14	=MAX(E14:I14)
C15	=MAX(E15:I15)
C16	=MAX(E16:I16)
C17	=MAX(E17:I17)
C18	=MAX(E18:I18)
C19	=MAX(E19:I19)

Named Ranges
Name	Refers To	Cells
_ID1	=Sheet2!$C$3
_ID2	=Sheet2!$C$6
_Max1	=Sheet2!$C$2
_Max2	=Sheet2!$C$5
headerrow	=Sheet2!$E$10:$I$10
IDRg	=Sheet2!$B$11:$B$19
WholeDataSet	=Sheet2!$E$11:$I$19