# Max IF formula

#### willow1985

##### Well-known Member
Hello,

I am trying to get a Max IF formula to work. At first I thought of using an index match with Max however I read online that the below formula would be a better fit:

=MAX(IF(('% Complete'!A1:A6=A1),'% Complete'!\$B\$1:\$B\$7))

I need the formula to return the Maximum percentage from the list below for each color match:

% Complete
A B
 Red 50% Blue 10% Green 90% Yellow 50% Red 100% Red 75% Yellow 25%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

This is what the results should be:
A B
 Red 100% Blue 10% Green 90% Yellow 50%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

However what I get is:

 Red 100% Blue 100% Green 100% Yellow 100%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Any help would be greatly appreciated

Thank you

Carla

#### willow1985

##### Well-known Member
Re: Need help with a Max IF formula

Thank you so much! I learned a lot today. I think I will use this formula more often. I like that the AGGREGATE ignores errors and that you can chose to have it return the 1st or 2nd Largest value etc.

Thank you very very much!!

Carla

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### DanteAmor

##### Well-known Member
Re: Need help with a Max IF formula

Use sumproduct formula:

=SUMPRODUCT(MAX(('Labor Input BLANK'!\$F\$3:\$F\$1000=D2)*('Labor Input BLANK'!\$H\$3:\$H\$1000)))

Other recommendations. Your current file is 17 megabytes

Do not copy formulas, or validations, or formats throughout the sheet, just copy what you occupy. If you delete all the rows you don't occupy you will have a 57Kb file. This makes it easier to work.

#### Fluff

##### MrExcel MVP, Moderator
Re: Need help with a Max IF formula

You're welcome & thanks for the feedback

#### willow1985

##### Well-known Member
Re: Need help with a Max IF formula

The worksheet is not mine but I will clean it up before I send it back to my college.

Thank you very much for all your help

Carla

Totally agree.

Replies
1
Views
42
Replies
1
Views
60
Replies
1
Views
245
Replies
1
Views
47
Replies
7
Views
659