How to Find Maximum Value in Excel with Condition (8 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In various calculations, we may need to calculate maximum values from any given dataset. Excel provides its built-in function which is the MAX function which returns the largest value from the passed value. Sometimes we may need to find the largest value based on criteria or conditions. In this article, we will show how to find the maximum value in Excel with a specific condition.


We will be using the MAX function to find out the maximum number of a series. Let’s see the concept of this function first.

MAX(number1, [number2], ...)

In this function’s parameter, we can pass the numbers and it will search or figure out the maximum possible number from them and give it as an output. 1 to 255 numbers for which you want to find the maximum value.

Let’s assume we have a dataset of some students with their ID, Names, Departments, semesters, CGPA, and Fees.

dataset to find maximum value in excel with condition


1. Applying MAX Function to Find Maximum Value in Excel with Condition

First of all, let’s demonstrate the function with an example. We will see how we can use it, like any other function, and find the maximum value here with a specific condition. In this case, we want to find the maximum fees for any specific semester.

Follow these steps to see how we can do that.

Steps:

  • First, select cell J5.
  • Then write down 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. Otherwise, FALSE in the same place.

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

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

  • After that, press Enter.

find maximum value in excel with condition with max function

This is how we can find the maximum value using the MAX function with a simple condition in Excel.


2. Combining Excel MAX and IF Functions to Find Maximum Value

For this process, we will find the maximum CGPA for a specific department. And we are going to use the IF function along with the MAX function for this one. The IF function takes a condition as the first argument, and a second argument to return if the condition is true. And an optional third argument in case the condition is false.

Follow these steps to see how we can combine these functions to find the maximum value in Excel with a similar condition.

Steps:

  • First, select cell J5.
  • Then 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 where the value 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.

  • After that, 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.


3. Joining Excel SUMPRODUCT and MAX Functions to Get Maximum Value

In this section let’s see how we can do the same task done in example 2 without an array formula. For this, we will need the SUMPRODUCT function. It takes the array in its parameter and returns the sum. Along with the function, we will utilize the MAX function to find the maximum value in Excel for a similar condition.

Again, our dataset will be the same as above. But we add one more. So totally our task is to find the largest CGPA for any specific Department and the Semester should be below our entered number.

Follow these steps to see how we can achieve that.

Steps:

  • First, select cell J6.
  • Then write down 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

  • Finally, press Enter.

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

Thus, Excel will give us the maximum value using the SUMPRODUCT function along with the MAX function for the specified condition.


4. Merging Excel MAX-IF Formula with OR Condition to Find Maximum Value

Now let’s try to find the maximum number or value conditionally with OR logic. Or logic means from two or multiple criteria at least one has to be met. We will use the same example that was used for the previous method but here our concern will be to find the maximum CGPA for two specific semesters.

Follow these steps to find the maximum value in Excel with the OR condition.

Steps:

  • First, select cell J5.
  • Now 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 then 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.

  • Then press Enter.

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

This is how we can find the maximum value with the help of the MAX and IF functions with an OR logic in Excel.


5. Inserting MAXIFS Function to Find Maximum Value with Condition

Now we can skip all the functions altogether to find the maximum value in Excel with the condition. Instead, we can use the MAXIFS function to find the maximum value with a similar condition. This function first takes a range from which it will pick the maximum value. Then it takes conditions as arguments in a pair of ranges and conditions. It then returns the maximum value from the range that meets all of the criteria.

Here is a detailed example of the usage of the function.

Steps:

  • First, select cell J6.
  • Then write down the following formula in it.

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

  • Finally, 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.


6. Obtaining Absolute Maximum Value with Excel ABS Function

Now there are a lot of uses for real numbers in a dataset. Suppose we have a dataset like this.

And there are cases where we just need to find the maximum outward deviation from zero. In other words, the absolute maximum. Using the MAX function in these cases will lead to the positive maximum value only. So we need to utilize the ABS function with it.

Follow these steps to find the maximum value in Excel with the help of these functions with the condition that it will be the absolute max.

Steps:

  • First, select cell D5.
  • Then 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.

  • Finally, press Enter on your keyboard.

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

As a result, we can find the maximum value in Excel with the condition of being absolute.


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

Similar to the previous case, there can be decimal values in a dataset where we need the upper or lower value of the decimal. For example, this dataset.

To find the maximum value in the Excel spreadsheet with the condition that it should be an integer, we need the INT function along with the MAX function in this case.

Follow these steps to see the formula, its usage, and its breakdown.

Steps:

  • First, select cell D5.
  • Next, write down 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.

  • Finally, press Enter.

find maximum integer value in excel with condition


8. Finding Maximum Value in Excel Ignoring Zero

Finally, let’s say there are negative values in a dataset along with zeros. We need to find the maximum value from the negative value but ignore the zero. Here is a dataset that matches the criteria.

Using the MAX function will lead to the outcome of zero every time. So we need the help of the IF function additionally. Or we can use the MAXIFS function ignoring them altogether.

Follow these steps to see the usage of both formulas to find the maximum value in Excel with the required criteria.

Steps:

  • First, select cell D5.
  • Then write down 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 values of the range where the condition is TRUE.

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

  • Now press Enter.

find maximum value in excel with condition ignoring zero

  • Also, you can use another formula. For that, select cell D9 and write down the following formula.

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

  • Finally, 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 Row with Max Value in Excel

Now let’s go back to the old dataset. With the help of some other functions, we can also find where the maximum value was in the dataset. For that, we need the MATCH function with the MAX function. We have discussed the MAX function throughout the whole article. Whereas the MATCH function takes a value to look for and an array to look into as arguments. Then it returns the relative position of the value.

Follow these steps to see the combination to find the row with the max value in Excel and its breakdown.

Steps:

  • First, select cell J6.
  • Then write down 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. Then it returns a value with either a FALSE where the value 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.

  • After that, press Enter.

  • Now select cell J6 and write down the following formula.

=MATCH(J5,F5:F17,0)

  • Finally, press Enter.

This is how we can find the relative position of the row with the max value in Excel.


Download Practice Workbook

You can download the workbook used for the demonstration from the link below.


Conclusion

So these were different formulas and their application to find the maximum value in Excel with the condition. Hopefully, you have understood the principle and can find the maximum value with your desired condition in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo