"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

 

[Special-K99]

"See attached test"

FYI
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[jxb]

Book1
	B	C	D	E	F	G	H	I	J	K
1
2	max =	0.933328
3	ID =	78
4	case =		return the value in row 7 corrpsonding to the max value for the ID found. In this case v5
5
6
7	ID	max		v1	v2	v3	v4	v5
8	1	0.909336		0.594634	0.356569	0.909336	0.813421	0.649909
9	2	0.885865		0.06891	0.885865	0.472114	0.323692	0.595647
10	3	0.491332		0.086735	0.164288	0.46222	0.013518	0.491332
11	78	0.933328		0.557731	0.659797	0.817564	0.797375	0.933328
12	99	0.918975		0.192214	0.512669	0.910371	0.201446	0.918975
13
Sheet1

Cell Formulas
Range	Formula
C2	=MAX(C8:C12)
C3	=INDEX(B8:B12,MATCH(C2,C8:C12,0))
C8	=MAX(E8:I8)
C9	=MAX(E9:I9)
C10	=MAX(E10:I10)
C11	=MAX(E11:I11)
C12	=MAX(E12:I12)

 

[jxb]

Had a go with one of the tools. See posted HTML above - A pain that one cannot attach xls directly anymore!

 

[Joe4]

A pain that one cannot attach xls directly anymore!

It never was possible.

 

[jxb]

Well it's been while and I am clearly going old then... Only managed to figure out about the copy-paste html thing!
Let's see if someone can get a smart way of extracting the data

 

[Fluff]

How about
=INDEX(E7:I7,SUMPRODUCT((E8:I12=MAX(E8:I12))*COLUMN(E8:I12))-COLUMN(E8:I12)+1)

 

[Eric W]

Two more options:

=INDEX(E7:I7,MATCH(C2,INDEX(E8:I12,MATCH(C2,C8:C12,0),0),0))

which assumes the C8:C12 values are present. Or

=INDEX(E7:I7,AGGREGATE(15,6,(COLUMN(E8:I12)-COLUMN(E8)+1)/(E8:I12=C2),1))

which just reads the E8:I12 values directly.

Note that in the unlikely event of a tie of the maximum value, Fluff's formula will return an error or an incorrect value, while these formulas will return the first match.

 

[jxb]

Thanks. Will take time to breakdown the formula to understand it. In the meantime I'll definitely save it for future use

Q: Is there not a way of only working on the row where the ID has been found? In other word I want to make use of the fact that I know the ID to work with
Why: For the test the selecting the whole array is fine I ultimately will have a number of max/ID/case to be found each wording on a subset of the data set. See example below
Mind you the test i just did based on your formula seems to work, so maybe I am over-thinking the whole think (as your solution seem to do the trick)

Book1
	B	C	D	E	F	G	H	I
2	max1=	0.933328
3	ID1 =	78
4	case =	v5	return the value in row 7 corrpsonding to the max value for the ID found. In this case v5
5	max2=	0.994172
6	ID1 =	103
7	case =	v4
8
9
10	ID	max		v1	v2	v3	v4	v5
11	1	0.909336		0.595	0.357	0.909	0.813	0.650
12	2	0.885865		0.565	0.886	0.472	0.324	0.596
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	0.837432		0.557	0.216	0.154	0.688	0.837
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
Sheet1

Cell Formulas
Range	Formula
C2	=MAX(C11:C15)
C3	=INDEX(B11:B15,MATCH(C2,C11:C15,0))
C4	=INDEX(E10:I10,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(E10:I10,SUMPRODUCT((E11:I19=MAX(E11:I19))*COLUMN(E11:I19))-COLUMN(E11:I19)+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)

 

[Fluff]

Eric's 1st formula will just look at the relevant row.
Also as he pointed out, if it's possible to have the same value twice, my suggestion will fail