Finding the maximum or minimum values in Excel is important when you’re working with a lot of data. Luckily, you can use formulas to find the cells that contain these maximum and minimum values. In this tutorial, I will show you the most effective ways to find the cell addresses that have both the maximum and minimum values in rows and columns. Find Cell Address From a Column of Numbers: Let’s look at a worksheet with sales values in column B, from row 4 to row 18. These values are random and can change. Please refer to the image for more details. column B row 4 to row 18 Formula to Find the Cell Address That Has the Maximum Value From Rows: =CELL("address",INDEX(B4:B18,MATCH(MAX(B4:B18),B4:B18,0))) =CELL("address",INDEX(B4:B18,MATCH(MAX(B4:B18),B4:B18,0))) MAX(B4:B18) – Finds the highest (maximum) value in the range B4:B18. MAX(B4:B18) – Finds the highest (maximum) value in the range B4:B18. MAX(B4:B18) B4:B18 MATCH(MAX(B4:B18), B4:B18, 0) :


MATCH looks for the maximum value (calculated by MAX(B4:B18)) in the range B4:B18.


The 0 in the MATCH function means it looks for an exact match.


MATCH returns the relative position of that maximum value in the range.


For example, if the maximum value is in cell B10 (which is the 7th cell in the range B4:B18), it returns 7.



INDEX(B4:B18, 7) – It returns a reference to the 7th cell in the range B4:B18 (which is B10). MATCH(MAX(B4:B18), B4:B18, 0) :


MATCH looks for the maximum value (calculated by MAX(B4:B18)) in the range B4:B18.


The 0 in the MATCH function means it looks for an exact match.


MATCH returns the relative position of that maximum value in the range.


For example, if the maximum value is in cell B10 (which is the 7th cell in the range B4:B18), it returns 7. MATCH(MAX(B4:B18), B4:B18, 0) MATCH looks for the maximum value (calculated by MAX(B4:B18)) in the range B4:B18.


The 0 in the MATCH function means it looks for an exact match.


MATCH returns the relative position of that maximum value in the range.


For example, if the maximum value is in cell B10 (which is the 7th cell in the range B4:B18), it returns 7. MATCH looks for the maximum value (calculated by MAX(B4:B18)) in the range B4:B18. MATCH looks for the maximum value (calculated by MAX(B4:B18)) in the range B4:B18. MATCH B4:B18 B4:B18 The 0 in the MATCH function means it looks for an exact match. The 0 in the MATCH function means it looks for an exact match. MATCH MATCH returns the relative position of that maximum value in the range. MATCH returns the relative position of that maximum value in the range. MATCH For example, if the maximum value is in cell B10 (which is the 7th cell in the range B4:B18), it returns 7. For example, if the maximum value is in cell B10 (which is the 7th cell in the range B4:B18), it returns 7. B4:B18 INDEX(B4:B18, 7) – It returns a reference to the 7th cell in the range B4:B18 (which is B10). INDEX(B4:B18, 7) B4:B18 CELL(“address”, …) – It returns the cell address of the cell that contains the maximum value. CELL(“address”, …) – It returns the cell address of the cell that contains the maximum value. CELL(“address”, …) Copy & paste the above formula to find the cell address containing the maximum value. The formula will return the result as “$B$8“. The value $B$8 indicates the location of the maximum value in the current worksheet. $B$8 Formula to Find the Cell Address That Has the Minimum Value From Rows: =CELL("address",INDEX(B4:B18,MATCH(MIN(B4:B18),B4:B18,0))) =CELL("address",INDEX(B4:B18,MATCH(MIN(B4:B18),B4:B18,0))) MIN(B4:B18): It finds the smallest (minimum) value in the range. MIN(B4:B18): It finds the smallest (minimum) value in the range. MIN(B4:B18) The above formula will find the cell address that contains the minimum value in the worksheets. The formula will return the result as “$B$11“. The value $B$11 indicates the location of the minimum value in the current worksheet. $B$11 $B$11 Find Cell Address From a Row of Numbers: Similarly, the sales values range from column C to column P, all in row 3. Please refer to the image below. column C column P row 3 Formula to Find the Cell Address That Has the Maximum Value From Columns: =CELL("address",INDEX(C3:P3,MATCH(MAX(C3:P3),C3:P3,0))) =CELL("address",INDEX(C3:P3,MATCH(MAX(C3:P3),C3:P3,0))) MAX(C3:P3) – Finds the highest (maximum) value in the range C3:P3. MAX(C3:P3) – Finds the highest (maximum) value in the range C3:P3. MAX(C3:P3) C3:P3 MATCH(MAX(C3:P3), C3:P3, 0) :


MATCH looks for the maximum value (calculated by MAX(C3:P3)) in the range C3:P3.


The 0 in the MATCH function means it looks for an exact match.


MATCH returns the relative position of that maximum value in the range.


For example, if the maximum value is in cell M3 (which is the 3rd cell in the range C3:P3), it returns 3.



INDEX(C3:P3, 3) – It returns a reference to the 3rd cell in the range C3:P3 (which is M3). MATCH(MAX(C3:P3), C3:P3, 0) :


MATCH looks for the maximum value (calculated by MAX(C3:P3)) in the range C3:P3.


The 0 in the MATCH function means it looks for an exact match.


MATCH returns the relative position of that maximum value in the range.


For example, if the maximum value is in cell M3 (which is the 3rd cell in the range C3:P3), it returns 3. MATCH(MAX(C3:P3), C3:P3, 0) MATCH looks for the maximum value (calculated by MAX(C3:P3)) in the range C3:P3.


The 0 in the MATCH function means it looks for an exact match.


MATCH returns the relative position of that maximum value in the range.


For example, if the maximum value is in cell M3 (which is the 3rd cell in the range C3:P3), it returns 3. MATCH looks for the maximum value (calculated by MAX(C3:P3)) in the range C3:P3. MATCH looks for the maximum value (calculated by MAX(C3:P3)) in the range C3:P3. MATCH C3:P3 The 0 in the MATCH function means it looks for an exact match. The 0 in the MATCH function means it looks for an exact match. MATCH MATCH returns the relative position of that maximum value in the range. MATCH returns the relative position of that maximum value in the range. MATCH For example, if the maximum value is in cell M3 (which is the 3rd cell in the range C3:P3), it returns 3. For example, if the maximum value is in cell M3 (which is the 3rd cell in the range C3:P3), it returns 3. C3:P3 INDEX(C3:P3, 3) – It returns a reference to the 3rd cell in the range C3:P3 (which is M3). INDEX(C3:P3, 3) C3:P3 CELL(“address”, …) – It returns the cell address of the cell that contains the maximum value. CELL(“address”, …) – It returns the cell address of the cell that contains the maximum value. CELL(“address”, …) Executing the above formula will return the cell address value that contains the maximum value from the different columns. For example, the formula will return the cell address “$M$3”, which contains the maximum value. $M$3 Formula to Find the Cell Address That Has the Minimum Value From Columns: =CELL("address",INDEX(C3:P3,MATCH(MIN(C3:P3),C3:P3,0))) =CELL("address",INDEX(C3:P3,MATCH(MIN(C3:P3),C3:P3,0))) MIN(C3:P3): It finds the smallest (minimum) value in the range. MIN(C3:P3): It finds the smallest (minimum) value in the range. MIN(C3:P3) Similarly, the formula will return the cell address that contains the minimum value from the multiple columns. For example, the formula will return the cell address “$J$3” which contains the minimum value from the columns. $J$3 Functions Used to Achieve This: Used Functions

Functions Used for



CELL

Returns information about the formatting, location, or contents of a cell.



INDEX

Returns the value of an element in a table or array, selected by row and column number.



MATCH

Searches for a specified item in a range of cells and returns the relative position of that item.



MIN

Returns the smallest number in a set of values.



MAX

Returns the largest number in a set of values. Used Functions

Functions Used for



CELL

Returns information about the formatting, location, or contents of a cell.



INDEX

Returns the value of an element in a table or array, selected by row and column number.



MATCH

Searches for a specified item in a range of cells and returns the relative position of that item.



MIN

Returns the smallest number in a set of values.



MAX

Returns the largest number in a set of values. Used Functions

Functions Used for Used Functions Used Functions Functions Used for Functions Used for CELL

Returns information about the formatting, location, or contents of a cell. CELL CELL CELL Returns information about the formatting, location, or contents of a cell. Returns information about the formatting, location, or contents of a cell. INDEX

Returns the value of an element in a table or array, selected by row and column number. INDEX INDEX INDEX Returns the value of an element in a table or array, selected by row and column number. Returns the value of an element in a table or array, selected by row and column number. MATCH

Searches for a specified item in a range of cells and returns the relative position of that item. MATCH MATCH MATCH Searches for a specified item in a range of cells and returns the relative position of that item. Searches for a specified item in a range of cells and returns the relative position of that item. MIN

Returns the smallest number in a set of values. MIN MIN MIN Returns the smallest number in a set of values. Returns the smallest number in a set of values. MAX

Returns the largest number in a set of values. MAX MAX MAX Returns the largest number in a set of values. Returns the largest number in a set of values. That’s it. This tutorial was originally published on How Do I Find Cell Contains Maximum or Minimum Values in Excel? How Do I Find Cell Contains Maximum or Minimum Values in Excel

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Excel Formulas for Assigning Categories by Value Range Using Functions!

How Do I Find the Cell That Contains the Maximum or Minimum Value in Excel?

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps