Learn How to Find the Max Value in a Worksheet and Return a Corresponding Value from a Function in Excel

In this article, you will learn how to use Excel formulas to find the maximum value in a worksheet and return it from a function. This can be useful when you want to perform calculations or comparisons based on the highest value in a range of cells.

There are different ways to achieve this goal, depending on your specific needs and preferences. In this article, we will cover the following methods:

• Using the MAX function to find the maximum value in a range
• Using the INDEX and MATCH functions to return a corresponding value from another column
• Using the VLOOKUP function to return a corresponding value from another column
• Using the MAX IF function to find the maximum value based on one or more conditions
• Using the MAXIFS function to find the maximum value based on multiple conditions

We will also provide examples and explanations for each method, as well as a table that summarizes the pros and cons of each method.

Method 1: Using the MAX Function

The simplest way to find the maximum value in a range of cells is to use the MAX function. The syntax of the MAX function is:

=MAX (number1, [number2], …)

The MAX function returns the largest number in a set of values. You can enter up to 255 numbers as arguments, either as constants, cell references, or ranges.

For example, suppose you have a worksheet that contains the sales data of four products in column A and their prices in column B, as shown below:

To find the maximum price in column B, you can use the following formula:

=MAX (B2:B5)

This formula returns 18, which is the highest value in the range B2:B5.

Method 2: Using the INDEX and MATCH Functions

If you want to find the maximum value in a range and return a corresponding value from another column, you can use a combination of the INDEX and MATCH functions. The syntax of the INDEX function is:

=INDEX (array, row_num, [column_num])

The INDEX function returns a value from a specific position in an array or range. You can specify the row number and the column number of the value you want to return.

The syntax of the MATCH function is:

=MATCH (lookup_value, lookup_array, [match_type])

The MATCH function returns the relative position of a value in a range or array. You can specify the value you want to find, the range or array where you want to look for it, and the type of match you want to perform.

By nesting the MATCH function inside the INDEX function, you can create a formula that looks up the maximum value in a range and returns a value from the same row in another column. For example, suppose you want to find the product name that corresponds to the maximum price in the previous example. You can use the following formula:

=INDEX (A2:A5,MATCH (MAX (B2:B5),B2:B5,0))

This formula works as follows:

• The MAX function returns the maximum value in the range B2:B5, which is 18.
• The MATCH function returns the relative position of 18 in the range B2:B5, which is 4.
• The INDEX function returns the value in the fourth row of the range A2:A5, which is D.

Therefore, the formula returns D, which is the product name that corresponds to the maximum price.

Method 3: Using the VLOOKUP Function

Another way to find the maximum value in a range and return a corresponding value from another column is to use the VLOOKUP function. The syntax of the VLOOKUP function is:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

The VLOOKUP function returns a value from a specific column in a table or range. You can specify the value you want to look for, the table or range where you want to look for it, the column number of the value you want to return, and the type of match you want to perform.

To use the VLOOKUP function for this purpose, you need to rearrange your data so that the column with the maximum values is the first column of the table or range. For example, suppose you swap the columns A and B in the previous example, so that the prices are in column A and the product names are in column B, as shown below:

To find the product name that corresponds to the maximum price, you can use the following formula:

=VLOOKUP (MAX (A2:A5), A2:B5, 2, FALSE)

This formula works as follows:

• The MAX function returns the maximum value in the range A2:A5, which is 18.
• The VLOOKUP function looks for 18 in the first column of the range A2:B5, and returns the value from the second column of the same row, which is D.
• The FALSE argument indicates that you want an exact match, not an approximate match.

Therefore, the formula returns D, which is the product name that corresponds to the maximum price.

Method 4: Using the MAX IF Function

If you want to find the maximum value in a range based on one or more conditions, you can use an array formula that combines the MAX and IF functions. The syntax of the IF function is:

=IF (logical_test, [value_if_true], [value_if_false])

The IF function returns a value based on a logical test. You can specify the condition to evaluate, the value to return if the condition is true, and the value to return if the condition is false.

By nesting the IF function inside the MAX function, you can create an array formula that returns the maximum value that meets one or more criteria. For example, suppose you want to find the maximum price for product A in the previous example. You can use the following formula:

=MAX (IF (B2:B5=”A”, A2:A5))

This formula works as follows:

• The IF function returns an array of values from the range A2:A5, where the corresponding values in the range B2:B5 are equal to “A”. The values that do not meet the condition are returned as FALSE. The result is: {10;FALSE;FALSE;FALSE}
• The MAX function returns the maximum value in the array, ignoring the FALSE values. The result is 10.

Note that this is an array formula, so you need to enter it by pressing Ctrl + Shift + Enter. Excel will automatically enclose the formula in curly braces, like this: {=MAX (IF (B2:B5=”A”, A2:A5))}

Method 5: Using the MAXIFS Function

If you have Excel 2019 or later, or Excel for Microsoft 365, you can use the MAXIFS function to find the maximum value in a range based on multiple conditions. The syntax of the MAXIFS function is:

=MAXIFS (max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

The MAXIFS function returns the maximum value in a range that meets one or more criteria. You can specify the range of values to find the maximum, the range of values to apply the criteria, and the criteria to use.

For example, suppose you want to find the maximum price for product A or B in the previous example. You can use the following formula:

=MAXIFS (A2:A5, B2:B5, “A”, B2:B5, “B”)

This formula works as follows:

• The MAXIFS function returns the maximum value in the range A2:A5, where the corresponding values in the range B2:B5 are either “A” or “B”. The result is 15.

Summary Table

The following table summarizes the pros and cons of each method discussed in this article: