Lookup + Max

[dothansouthern]

I've created a spreadsheet to tabulate scores for a band competition.
I need formulas to extract the high score in different classes and
categories.

Class Band "Drum Major" "Color Guard" "Dance Line"
Majorettes
1A Abbeville 85 86
1A New Brockton 85 73
85 86
1A Dale County 82 90 86
2A Early County 90 93 78
2A W.S. Neal 92 93 90
2A Russell County 97 88
3A Geneva 85 81 88
3A Valley 87 97 88


Best in Class 1A 2A 3A
Drum Major Rehobeth Early County
Geneva
Color Guard Freeport W.S. Neal Geneva
Dance Line Saint James W.S. Neal
Majorettes Slocomb W.S. Neal
Andalusia
Ex. I need the formula to extract the high score for Drum Major in
Class 1A. How do I do this? Thanks

 

[T. Valko]

I need the formula to extract the high score for Drum Major

in Class 1A.

A2:A9 = class
C1:E1 = column headers: Drum Major, Color Guard, Dance Line
C2:E9 = scores

Try this array formula** :

=MAX(IF(A2:A9="1A",C2:C9))

Or, to make it flexible where you can choose the category, enter the
category you want in a cell:

A25 = Drum Major

Also an array formula** :

=MAX(IF(A2:A9="1A",INDEX(C2:E9,,MATCH(A25,C1:E1,0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[dothansouthern]

A2:A9 = class
C1:E1 = column headers: Drum Major, Color Guard, Dance Line
C2:E9 = scores

Try this array formula** :

=MAX(IF(A2:A9="1A",C2:C9))

Or, to make it flexible where you can choose the category, enter the
category you want in a cell:

A25 = Drum Major

Also an array formula** :

=MAX(IF(A2:A9="1A",INDEX(C2:E9,,MATCH(A25,C1:E1,0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP








- Show quoted text -

How do I get it to output the name of the school.

 

[T. Valko]

How do I get it to output the name of the school.

What if there are ties like in your sample?

1A Abbeville 85
1A New Brockton 85
1A Dale County 82

Do you want both schools?

--
Biff
Microsoft Excel MVP

A2:A9 = class
C1:E1 = column headers: Drum Major, Color Guard, Dance Line
C2:E9 = scores

Try this array formula** :

=MAX(IF(A2:A9="1A",C2:C9))

Or, to make it flexible where you can choose the category, enter the
category you want in a cell:

A25 = Drum Major

Also an array formula** :

=MAX(IF(A2:A9="1A",INDEX(C2:E9,,MATCH(A25,C1:E1,0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP








- Show quoted text -

How do I get it to output the name of the school.

 

[dothansouthern]

What if there are ties like in your sample?

1A Abbeville 85
1A New Brockton 85
1A Dale County 82

Do you want both schools?

--
Biff
Microsoft Excel MVP








How do I get it to output the name of the school.- Hide quoted text -

- Show quoted text -

Yesn I'll need both schools. Thanks

 

[T. Valko]

Ok, it's complicated!

Class in column A
School in colimn B
Scores in column C

Assume you enter the array formula** in A10.

=IF(ROWS(A$10:A10)<=SUM((A$2:A$7="1A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7)))),INDEX(B$2:B$7,SMALL(IF((A$2:A$7="1A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7))),ROW(B$2:B$7)),ROWS(A$10:A10))-MIN(ROW(B$2:B$7))+1),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

What if there are ties like in your sample?

1A Abbeville 85
1A New Brockton 85
1A Dale County 82

Do you want both schools?

--
Biff
Microsoft Excel MVP








How do I get it to output the name of the school.- Hide quoted text -

- Show quoted text -

Yesn I'll need both schools. Thanks

 

[dothansouthern]

Ok, it's complicated!

Class in column A
School in colimn B
Scores in column C

Assume you enter the array formula** in A10.

=IF(ROWS(A$10:A10)<=SUM((A$2:A$7="1A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7­)))),INDEX(B$2:B$7,SMALL(IF((A$2:A$7="1A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2­:C$7))),ROW(B$2:B$7)),ROWS(A$10:A10))-MIN(ROW(B$2:B$7))+1),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP









Yesn I'll need both schools. Thanks- Hide quoted text -

- Show quoted text -

Thanks for all you help. How do display a blank cell for the output if
I don't have any scores for that column. Also, How do I display the
high scores if they are tied. Currently, it displays the first high
score.

 

[T. Valko]

This just keeps getting more and more complicated! (I love complex stuff!)

To return a blank when no scores are entered (still array entered):

=IF(SUMIF(A$2:A$7,"1A",C$2:C$7)=0,"",IF(ROWS(A$10:A10)<=SUM((A$2:A$7="1A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7)))),INDEX(B$2:B$7,SMALL(IF((A$2:A$7="1A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7))),ROW(B$2:B$7)),ROWS(A$10:A10))-MIN(ROW(B$2:B$7))+1),""))

How do I display the high scores if they are tied.
Currently, it displays the first high score.

Not sure what you mean with that. Isn't that the original question you
asked?

Array entered:

=MAX(IF(A2:A9="1A",C2:C9))


--
Biff
Microsoft Excel MVP

Ok, it's complicated!

Class in column A
School in colimn B
Scores in column C

Assume you enter the array formula** in A10.

=IF(ROWS(A$10:A10)<=SUM((A$2:A$7="1A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7­)))),INDEX(B$2:B$7,SMALL(IF((A$2:A$7="1A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2­:C$7))),ROW(B$2:B$7)),ROWS(A$10:A10))-MIN(ROW(B$2:B$7))+1),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP









Yesn I'll need both schools. Thanks- Hide quoted text -

- Show quoted text -

Thanks for all you help. How do display a blank cell for the output if
I don't have any scores for that column. Also, How do I display the
high scores if they are tied. Currently, it displays the first high
score.

 

[dothansouthern]

This just keeps getting more and more complicated! (I love complex stuff!)

To return a blank when no scores are entered (still array entered):

=IF(SUMIF(A$2:A$7,"1A",C$2:C$7)=0,"",IF(ROWS(A$10:A10)<=SUM((A$2:A$7="1A")*­(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7)))),INDEX(B$2:B$7,SMALL(IF((A$2:A$7="1­A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7))),ROW(B$2:B$7)),ROWS(A$10:A10))-M­IN(ROW(B$2:B$7))+1),""))


Not sure what you mean with that. Isn't that the original question you
asked?

Array entered:

=MAX(IF(A2:A9="1A",C2:C9))

--
Biff
Microsoft Excel MVP










Thanks for all you help. How do display a blank cell for the output if
I don't have any scores for that column. Also, How do I display the
high scores if they are tied. Currently, it displays the first high
score.- Hide quoted text -

- Show quoted text -

What I meant to ask is, I need to know when there is a tie score. If
two schools have the same high score we need to know. Somehow I would
like for it to highlight in red if there are two high scores. Thanks

 

[T. Valko]

Ok, try this...

Class in the range A2:A9
Scores in the range C2:C9

Assuming there are no empty cells in C2:C9

Select the range C2:C9
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:

=AND(C2=MAX(IF(A$2:A$9=A2,C$2:C$9)),SUM((A$2:A$9=A2)*(C$2:C$9=MAX(IF(A$2:A$9=A2,C$2:C$9))))>1)

Click the Format button
Select the Font tab
Select a nice shade of RED and maybe BOLD
OK out

--
Biff
Microsoft Excel MVP

This just keeps getting more and more complicated! (I love complex stuff!)

To return a blank when no scores are entered (still array entered):

=IF(SUMIF(A$2:A$7,"1A",C$2:C$7)=0,"",IF(ROWS(A$10:A10)<=SUM((A$2:A$7="1A")*­(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7)))),INDEX(B$2:B$7,SMALL(IF((A$2:A$7="1­A")*(C$2:C$7=MAX(IF(A$2:A$7="1A",C$2:C$7))),ROW(B$2:B$7)),ROWS(A$10:A10))-M­IN(ROW(B$2:B$7))+1),""))


Not sure what you mean with that. Isn't that the original question you
asked?

Array entered:

=MAX(IF(A2:A9="1A",C2:C9))

--
Biff
Microsoft Excel MVP










Thanks for all you help. How do display a blank cell for the output if
I don't have any scores for that column. Also, How do I display the
high scores if they are tied. Currently, it displays the first high
score.- Hide quoted text -

- Show quoted text -

What I meant to ask is, I need to know when there is a tie score. If
two schools have the same high score we need to know. Somehow I would
like for it to highlight in red if there are two high scores. Thanks