index match max!

[justme]

=INDEX(A1:F2,MATCH(MAX(A2:F2),A2:F2,0),1)


The desriptions are in a row in cells A through F
The values are in the cells below

I want to find the max value and return the description above.

I come up with the formula above, which is not giving me the correct answer. I get the answer of 5 as shown below.

A B C D E F
5 8 7 1 3 5


5

 

[btadams]

I don't mean to beat a dead horse, but if you open a new workbook and enter the following into A1:F2

Col 1 Col 2 Col 3 Col 4 Col 5 Col 6
0.879 0.611 0.471 0.202 0.934 0.087

Then name A1:F1 "Descriptions" and A2:F2 "CValues". Then in cell H2 put the formula

=INDEX(Descriptions,MATCH(MAX(cvalues),cvalues,0))

it gives you an error? It should return "Col 5"

 

[justme]

Yes, I can get it to work over a single sheet, but when the values are over multiple sheets I get the error codes.

Looking up the max(cvalues) works. I put =max(cvalues) in a cell and it works, however the the index statement returns a value error.

 

[justme]

PS - Thanks for checking back btadams

 

[Jonmo1]

yeah, lookups and Indexes don't work over multiple sheets..
I.E.

=INDEX(Sheet1:Sheet10!A1:B100,row,column)

 

[justme]

Suggestions?

 

[Jonmo1]

I think this will require VBA macro.

Let's clarify what needs to be done first.

First, let's stop using named ranges for now. That is a rule of thumb for me.
Get your function working with hard coded ranges first, THEN add in the named ranges for dynamics.

Now, what does it need to do?
It needs to find the max value in A2:F2 (among several sheets).
Then return the corresponding value from A1:F1

right?

OK, what sheets are we working on specifically.
Are they all next to each other in a row?
What is the name of the first and last sheets?

 

[justme]

All sheets are next to each other,

Sheets are between first and last.

Final user said they may want to have multiple ranges. In this case, I was going to have first,sheets, group1,sheets, group2,sheets, group3,sheets, last.

That way I could still have a max(first:last) and also Max(first:group1), etc.

If we can get one range to work, it should be easy to build the rest - right? (hopefully)!

 

[Aladin Akyurek]

If my information is found in multiple worksheets and I name some ranges. My test example has descriptions in cell C1 and Values in C2. I've named the ranges in Sheets 1-4. Descriptions= first:last!$C1, CValues = first:last!$C2

I am able to find: Max(CValues).

Now I want to find the description for the Max(CValues)

I've tried: =INDEX(Descriptions,MATCH(MAX(cvalues),cvalues,0)). This returns a #value!error.

I've also referenced the cell where I tested to see if it would find the max value over multiple worksheets. In cell A4 I have entered: max(cvalues) edit: (This returns the #Value! error)

Then my equation became: =index(descriptions,match(a4),cvalues,0))

This returns the error that I've entered too few arguemens for this function.

Likewise with the lookup function: =LOOKUP(MAX(cvalues),cvalues,Descriptions)

Lets say that you have 4 sheets, Sheet1, Sheet2, Sheet3, and Sheet4.

Lets say that we have a sheet called Processing.

Enter these sheet names in A2:A5.
Select A2:A5, name the selection SheetList.

Lets say that we have the following set of values across our sheets:

Sheet1!C1 = ADF, Sheet1!C2 = 2
Sheet2!C1 = CYX, Sheet2!C2 = 5
Sheet3!C1 = FXA, Sheet2!C2 = 4
Sheet3!C1 = DBA, Sheet4!C2 = 5

Processing

D2:

Control+shift+enter, not just enter:

=MAX(N(INDIRECT("'"&SheetList&"'!C2")))

D3, just enter:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!C2"),D2))

D4:

Control+shift+enter, not just enter:

=IF(ROWS($D$4:D4)<=$D$3,INDEX(T(INDIRECT("'"&SheetList&"'!C1")),
    SMALL(IF(N(INDIRECT("'"&SheetList&"'!C2"))=$D$2,
      ROW(SheetList)-ROW(INDEX(SheetList,1,1))+1),
        ROWS($D$4:D4))),"")

and copy down.

Here is an exhibit of the Processing sheet:

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Sheet1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla="=MAX(N(INDIRECT("'"&$A$2:$A$5&"'!C2")))" x:arrayrange="D2">5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Sheet2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla="=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A$5&"'!C2"),D2))">2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Sheet3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla="=IF(ROWS($D$4:D4)<=$D$3,INDEX(T(INDIRECT("'"&$A$2:$A$5&"'!C1")),SMALL(IF(N(INDIRECT("'"&$A$2:$A$5&"'!C2"))=$D$2,ROW($A$2:$A$5)-ROW(INDEX($A$2:$A$5,1,1))+1),ROWS($D$4:D4))),"")" x:arrayrange="D4">CYX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Sheet4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla="=IF(ROWS($D$4:D5)<=$D$3,INDEX(T(INDIRECT("'"&$A$2:$A$5&"'!C1")),SMALL(IF(N(INDIRECT("'"&$A$2:$A$5&"'!C2"))=$D$2,ROW($A$2:$A$5)-ROW(INDEX($A$2:$A$5,1,1))+1),ROWS($D$4:D5))),"")" x:arrayrange="D5">DBA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla="=IF(ROWS($D$4:D6)<=$D$3,INDEX(T(INDIRECT("'"&$A$2:$A$5&"'!C1")),SMALL(IF(N(INDIRECT("'"&$A$2:$A$5&"'!C2"))=$D$2,ROW($A$2:$A$5)-ROW(INDEX($A$2:$A$5,1,1))+1),ROWS($D$4:D6))),"")" x:arrayrange="D6"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR></TBODY></TABLE>

 

[Jonmo1]

Well, this may be a start..
IMPORTANT
You didn't specify where the resulting value should be placed,
so this code will put it in A1 on the active sheet (whatever sheet is active at the time you run the macro).

Sub FindMax()
dim Smax as Long, Tmax as Long, Tsheet as Long, TmaxCol as Long, i as Long
Smax = 0
Tmax = 0
Tsheet = 0
For i = Sheets("First").Index To Sheets("Last").Index
    Smax = Application.Max(Sheets(i).Range("A2:F2"))
    If Smax > Tmax Then
        Tmax = Smax
        Tsheet = i
    End if
Next i
TmaxCol = Application.Match(Tmax,Sheets(Tsheet).Range("A2:F2"),0)
ActiveSheet.Range("A1").Value = Sheets(Tsheet).Cells(1,TmaxCol)
End Sub

 

[justme]

I see what you have done.

You have found the max value.
You have found the number of occurences of the max value
and the next lines tell you which sheets they occur on.

Excellent!!!

I've duplicated your example on my computer and will now try it out on the actual data.

Thank you so much!