How to Find Maximum Value in Excel with Condition: 8 Examples

Method 1 – Applying the MAX Function to Find a Maximum Value in Excel with Condition

Steps:

Select cell J5.
Use the following formula in it.

=MAX((E5:E17=J4)*G5:G17)

Breakdown of the Formula

E5:E17=J4 is a conditional argument that returns a boolean array with TRUE if the consequent number of the range E5:E17 matches the value of cell J4.

(E5:E17=J4)*G5:G17 returns another array with the boolean value array from the previous output (converted into 0 or 1) multiplied with the range G5:G17. So, it returns either a zero or the value of the semester.

Finally, MAX((E5:E17=J4)*G5:G17) returns the maximum of those numbers.

Press Enter.

find maximum value in excel with condition with max function

Method 2 – Combining Excel MAX and IF Functions to Find a Maximum Value

Steps:

Select cell J5.
Insert the following formula.

=MAX(IF(D5:D17=J4, F5:F17))

Breakdown of the Formula

IF(D5:D17=J4, F5:F17) checks where the value of cell J4 matches within the range D5:D17. Then it returns a value with either a FALSE that doesn’t match or a value from the same position in the range F5:F17.

Then MAX(IF(D5:D17=J4, F5:F17)) returns the maximum value within the array.

Press Enter.

find maximum value in excel with condition with max and if functions

This is another way to find the maximum value in Excel with a similar condition.

Method 3 – Joining Excel SUMPRODUCT and MAX Functions to Get Maximum Value

Steps:

Select cell J6.
Apply the following formula in it.

=SUMPRODUCT(MAX(((D5:D17=J4)*(E5:E17<J5)*(F5:F17))))

Breakdown of the Formula

D5:D17=J4 is a condition and returns an array with either a TRUE or FALSE value depending on whether values in the range D5:D17 match with the cell value of J4.

E5:E17<J5 is a condition and returns an array with either a TRUE or FALSE value depending on whether values in the range E5:E17 is less than the cell value of J4.

(D5:D17=J4)*(E5:E17<J5)*(F5:F17) multiplies all of the array

Press Enter.

find maximum value in excel with condition with sumproduct and max functions

Method 4 – Merging Excel MAX-IF Formula with an OR Condition to Find Maximum Value

Steps:

Select cell J5.
Insert the following formula in it.

=MAX(IF((E5:E17=J4) + (E5:E17=L4), F5:F17))

Breakdown of the Formula

E5:E17=J4 and E5:E17=L4 are two conditions that return two boolean arrays.

IF((E5:E17=J4) + (E5:E17=L4), F5:F17) checks if the boolean summation of the arrays is TRUE or FALSE. It returns the values from the F5:F17 range from appropriate places.

Finally, MAX(IF((E5:E17=J4) + (E5:E17=L4), F5:F17)) returns the maximum value from the array of the previous output.

Press Enter.

find maximum value in excel with condition with max and if functions combining in or logic

Method 5 – Inserting MAXIFS Function to Find the Maximum Value with Condition

Steps:

Select cell J6.
Use the following formula in it.

=MAXIFS(G5:G17, D5:D17, J4, E5:E17, J5)

Press Enter on your keyboard.

find maximum value in excel with condition with maxifs function

As a consequence, we will find the maximum value from the range G5:G17 with the condition that both the value of cells J4 and J5 match within the range G5:G17 and D5:D17 in the Excel spreadsheet.

Method 6 – Obtaining Absolute Maximum Value with Excel ABS Function

Steps:

Select cell D5.
Insert the following formula in the cell.

=MAX(ABS(B5:B11))

Breakdown of the Formula

First, ABS(B5:B11) takes all the absolute value of the range B5:B11.

MAX(ABS(B5:B11)) then picks out the maximum value from the array.

Press Enter on your keyboard.

find absolute maximum value in excel with condition with max and abs functions

Find the maximum value in Excel with the condition of being absolute.

Method 7 – Getting Maximum Value in One Column When Number Is Integer Only

Steps:

Select cell D5.
Use the following formula.

=MAX(INT(B5:B11))

Breakdown of the Formula

INT(B5:B11) returns an array consisting of the round numbers from the range B5:B11.

Then MAX(INT(B5:B11)) function returns the maximum value from that array.

Press Enter.

find maximum integer value in excel with condition

Method 8 – Finding the Maximum Value in Excel Ignoring Zero

Steps:

Select cell D5.
Apply the following formula in it.

=MAX(IF(B5:B11<>0, B5:B11))

Breakdown of the Formula

IF(B5:B11<>0, B5:B11) checks where the value in the range B5:B11 is not zero and returns the range values where the condition is TRUE.

MAX(IF(B5:B11<>0, B5:B11)) then returns the maximum value from the output array.

Press Enter.

find maximum value in excel with condition ignoring zero

Use another formula. Select cell D9 and write down the following formula.

=MAXIFS(B5:B11,B5:B11,"<>0")

Press Enter.

find maximum value in excel with condition ignoring zero with maxifs

This is how we can find the maximum value in Excel with the condition that we are ignoring zeros.

How to Find the Row with the Max Value in Excel

Steps:

Select cell J6.
Use the following formula in it.

=MAX(IF(D5:D17=J4, F5:F17))

Breakdown of the Formula

IF(D5:D17=J4, F5:F17) checks where the value of cell J4 matches within the range D5:D17. It returns a value with either a FALSE that doesn’t match or a value from the same position in the range F5:F17.

Then MAX(IF(D5:D17=J4, F5:F17)) returns the maximum value within the array.