How to find maximum value in a column Using MAX, MATCH and ADDRESS Function in Excel

============================================================
============================================================
In this video, we will learn how to find the cell address of the maximum value in a column using MAX, MATCH, and ADDRESS Function in Excel. Locate Maximum Value Formula is a formula that can be used to identify the cells where there are max values from a given range of cells. Locate Max Values Formula will help you determine which items have been sold with highest price or weight etc. Locate Maximum Value Formula is found on Data tab under Analysis group.

First lets understand syntax of all these three functions;

MAX Function;

Syntax Of MAX Function is;

=MAX(number, [num],….)

Let's understand Locate Maximum Value Formula with an example;

In this formula, we have used the MAX function to determine the maximum value in a given range. In Locate Max Values Formula A:B refers to cell address of first row and second column which is being selected from the data set.

Now let's understand how the MATCH function works in Locate Maximum Value Formula.

MATCH Function;

The syntax of the MATCH function is:

=MATCH(lookup_value, lookup_array, match_type)

In Locate Max Values Formula A: B, lookup_value will be the value that we want to find the maximum for and lookup_array will be the range of cells containing the data that we are looking for a max value in. The match_type argument determines how Excel matches values in the two arrays. There are three possible arguments for this parameter:

-Exact Matching (match type number is 0): This option looks only for an exact match between lookup_value and lookup_array.

-Approximate Matching (match type number is greater than 0): This option allows Excel to find the largest value that is less than or equal to lookup_value in lookup_array.

-Wildcard Matching (*): With this option, Excel will look for a match of any characters at all in lookup_value within lookup_array.

In our Locate Maximum Value Formula A:B, since we want to find the maximum value in a given range, we will use the approximate matching option which is greater than 0.

Now let's understand how the ADDRESS function works in Locate Maximum Value Formula;

ADDRESS Function;

The syntax of the ADDRESS function is:

=ADDRESS(row_num, column_num)

In Locate Max Values Formula A:B, row_num will be the first-row number in the data set that we want to find the maximum value from, and column_num will be the first column number in the data set.

Now let's take a look at an example in the above video of how to use all these three functions together in Locate Maximum Value Formula.
Table Of Contents
00:00 Introduction
00:13 Locate maximum Value using MAX, MATCH, ADDRESS Function
02:40 Conclusion

Other Playlists:-

[Zynxs]

how would you make the function output the person's name instead of the number?

[purefiregreen]

Jane and John have the same max of 925. How do you do a text join in a cell to show that both have the max?

[harsinchh]

Sir,
A B C
20 40 30
(10) 50 [60
20 30 20
50 10 10
60 10 80
30 (90) 50]
20 20 70
From column A min value 10 to column b max value 90, how can I sum column c all cells from max value to min value at column A to column B.