How to Find the Maximum Value Based on a Condition in Excel

How to Find the Maximum Value Based on a Condition in Excel. In Excel, you may come across situations where you need to find the maximum value that satisfies a specific condition. This can be achieved using an array formula. Here’s how you can do it:

How to Find the Maximum Value Based on a Condition in Excel

In Excel, you may come across situations where you need to find the maximum value that satisfies a specific condition. This can be achieved using an array formula. Here’s how you can do it:

Scenario: Let’s say you have a dataset containing product IDs and their corresponding expiry dates, and you want to find the earliest expiry date for a specific product.

Generic Formula: { =MAX(IF(range = criteria, values)) }

• range = criteria: This represents the condition you want to apply to the array.
• values: This is the array where you want to find the maximum value.

Note: Remember to enter this formula as an array formula by pressing Ctrl + Shift + Enter, instead of just pressing Enter.

Example: Assuming you have a list of product IDs (in column A) and their expiry dates (in column B), and you want to find the earliest expiry date for a specific product ID (entered in cell G7).

Use the formula: { =MAX(IF(A2:A17 = G7, B2:B17)) }

Explanation: The IF function checks each value in the range A2:A17 (product IDs) against the value in G7 (specific product ID). If a match is found, it returns the corresponding value from the range B2:B17 (expiry dates). The result is an array of values.

The MAX function then finds the maximum value from the returned array, which represents the earliest expiry date for the specific product ID.

Remember to use Ctrl + Shift + Enter when entering the formula as an array formula.

By copying the formula to the next cell, you can find the earliest expiry date for the next product ID.

That’s it! You have successfully found the maximum value based on a condition using an array formula in Excel.

Scenario: Let’s say you have a dataset containing product IDs and their corresponding expiry dates, and you want to find the earliest expiry date for a specific product.

Generic Formula: { =MAX(IF(range = criteria, values)) }

• range = criteria: This represents the condition you want to apply to the array.
• values: This is the array where you want to find the maximum value.

Note: Remember to enter this formula as an array formula by pressing Ctrl + Shift + Enter, instead of just pressing Enter.

Example: Assuming you have a list of product IDs (in column A) and their expiry dates (in column B), and you want to find the earliest expiry date for a specific product ID (entered in cell G7).

Use the formula: { =MAX(IF(A2:A17 = G7, B2:B17)) }

Explanation: The IF function checks each value in the range A2:A17 (product IDs) against the value in G7 (specific product ID). If a match is found, it returns the corresponding value from the range B2:B17 (expiry dates). The result is an array of values.

The MAX function then finds the maximum value from the returned array, which represents the earliest expiry date for the specific product ID.

Remember to use Ctrl + Shift + Enter when entering the formula as an array formula.

By copying the formula to the next cell, you can find the earliest expiry date for the next product ID.

That’s it! You have successfully found the maximum value based on a condition using an array formula in Excel.