Find the address of the max value in range in Microsoft Excel

In this article, you will learn very tricky and useful Excel function to find out the address of the maximum value in a range. To find out the address of maximum valued cell, we are going to use combination of frequently used functions: - Max, Cell, Index and Match functions.

Now let’s see what all formulas will do to solve the problem

  1. How Max function will help in this problem?

Max function will find out the maximum value.

  1. What Cell function will do in this problem?

Cell function will help to get the cell address.

  1. How Index and Match function will perform?

Index and Match function will look up the value and provide the cell to Cell function to return the cell Address.

Let’s take an example and then understand how it will work?

We have product revenue data in Excel in which we want to find out the Maximum value containing cell address. In data column, C contains product name, column D contains revenue, column E contains % of contribution, and column F contains contribution amount.

 

image 1

First we will return maximum value, follow below steps:-

  • Enter the formula in cell D19
  • =MAX(D5:D16), Press Enter
  • The function will return the maximum value

 

image 2

 

Now we will enter the formula to find out the address

  • Enter the formula in cell D20
  • =CELL("address",INDEX(D5:D16,MATCH(D19,D5:D16,0)))Press Enter.
  • The function will return address of the maximum value.

 

image 3

 

In this way, we can find the address of the value in the range in Microsoft Excel by using the functions.

image 48

If you liked our blog, please like us @ Facebook or you can follow us @ Twitter. Also, we are interested in knowing your views on our blogs and what type of blogs you liked the most or where we need to improve in the blogs for your better understanding.

Comments

  1. "Como podria encotrar mediante una formula en una columna el numero maximo Anterior.

    Ejemplo: 1-2-45-46-25-75-85-47-78= 85Max , 78Max Min"

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.