# 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.

## 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.

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.

## 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.

## 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.

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 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.

## 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.

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

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

• Press Enter.

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.

• Press Enter.

• Select cell J6 and use the following formula.

`=MATCH(J5,F5:F17,0)`

• Press Enter.

## Related Articles

<< Go Back to Excel MAX Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!