OfficeTips Home || VBA Section || General Section ||  Download Section  ||  Privacy Policy

Get the Address Of The Cell That Contains The Maximum Value

We know that to find the Maximum value in a given range we use the Max worksheet function.
=MAX(<range>) 
 
where <range> is the range of cells containing the values. We have also seen how to sum up top N values in a given range of cells in an earlier tip. Now in some cases we might be more interested in obtaining the cell address of the cell containing the maximum value rather than the value itself. We can determine that by using the  use the following formula :
 
=ADDRESS(MATCH(MAX(<range>),<range>,0)+ROW(<range>)-1,COLUMN(<range>))
 
<range> represents the range of cells to look up. Similarly you can also use the above formula with the MIN function, which finds the cell address of the smallest value in a range of cells.

 

 

Copyright 1999-2014 (c) Shyam Pillai. All rights reserved.