# Thread: "double" INDEX & MATCH search? Thanks: 0 Likes:  3 Post #5308654 (1)Post #5308617 (1)Post #5308627 (1)

1. ## "double" INDEX & MATCH search?

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?

2. ## Re: "double" INDEX & MATCH search?

"See attached test"

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

3. ## Re: "double" INDEX & MATCH search?

BCDEFGHIJK
1
2max =0.933328
3ID =78
4case =return the  value in row 7 corrpsonding to the max value for the ID found. In this case v5
5
6
7IDmaxv1v2v3v4v5
810.9093360.5946340.3565690.9093360.8134210.649909
920.8858650.068910.8858650.4721140.3236920.595647
1030.4913320.0867350.1642880.462220.0135180.491332
11780.9333280.5577310.6597970.8175640.7973750.933328
12990.9189750.1922140.5126690.9103710.2014460.918975
13

Sheet1

Worksheet Formulas
CellFormula
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)

4. ## Re: "double" INDEX & MATCH search?

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

5. ## Re: "double" INDEX & MATCH search?

A pain that one cannot attach xls directly anymore!
It never was possible.

6. ## Re: "double" INDEX & MATCH search?

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

7. ## Re: "double" INDEX & MATCH search?

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

8. ## Re: "double" INDEX & MATCH search?

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.

9. ## Re: "double" INDEX & MATCH search?

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)

BCDEFGHI
2max1=0.933328
3ID1 =78
4case =v5return the  value in row 7 corrpsonding to the max value for the ID found. In this case v5
5max2=0.994172
6ID1 =103
7case =v4
8
9
10IDmaxv1v2v3v4v5
1110.9093360.5950.3570.9090.8130.650
1220.8858650.5650.8860.4720.3240.596
1330.87970.0870.1640.8800.0140.491
14780.9333280.5580.6600.8180.7970.933
15990.9189750.1920.5130.9100.2010.919
161010.985620.9860.6370.3940.0410.774
171020.8374320.5570.2160.1540.6880.837
181030.9941720.2350.7960.5500.9940.234
191040.9502870.6130.4800.5560.9500.056

Sheet1

Worksheet Formulas
CellFormula
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)

10. ## Re: "double" INDEX & MATCH search?

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