Thread: "double" INDEX & MATCH search? Thanks: 0 Likes:  3 Post #5308617 (1)Post #5308627 (1)Post #5308654 (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?  Reply With Quote

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  Reply With Quote

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)  Reply With Quote

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!  Reply With Quote

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

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

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  Reply With Quote

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

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

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.  Reply With Quote

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)  Reply With Quote

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  Reply With Quote

User Tag List

Tags for this Thread

index, match, max, row  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•