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

**Download the Practice Workbook**

## Related Articles

- Excel MIN and MAX in Same Formula
- How to Set a Minimum and Maximum Value in Excel
- How to Cap Percentage Values Between 0 and 100 in Excel
- Find Max Value and Corresponding Cell in Excel
- How to Find Max Value in Range with Excel Formula

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