How do I find the cell that contains the maximum or minimum value in Excel?

Finding the maximum or minimum values ​​in Excel is important when it works with a lot of data. Fortunately, you can use formulas to find cells that contain these maximum values ​​and minimum. In this tutorial, I will show you the most effective way to find cell addresses that have maximum values ​​and minimum rows and columns.

Look for the cell address from a column of numbers:

Let’s take a look at a worksheet with sales values ​​in The column bfrom Row 4 to grade 18. These values ​​are random and can change. Please refer to the image for more details.

A formula to find the address of the cell that has the maximum value of the rows:

=CELL("address",INDEX(B4:B18,MATCH(MAX(B4:B18),B4:B18,0)))

• Max (B4: B18) – He finds the highest value (maximum) in the range B4: B18.

• Match (Max (B4: B18), B4: B18, 0) :

  • match Looking for the maximum value (calculated by the maximum)B4: B18In the range B4: B18.

  • 0 in match The job means that it is looking for an accurate match.

  • match Return the relative position to that maximum value in the range.

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

• Index (B4: B18, 7) Returns a reference to the seventh cell in the range B4: B18 (It is B10).

• Cell (“Address”, …) The cell address of the cell that contains the maximum value is restored.

Copy and paste the above formula to find the address of the cell that contains the maximum value. The formula will restore the result as “$ b $ 8“The value is $ B $ 8 indicates the maximum value of the value of the current worksheet.

The formula to find the address of the cell that contains the minimum value of the rows:

=CELL("address",INDEX(B4:B18,MATCH(MIN(B4:B18),B4:B18,0)))

• Min (B4: B18): He finds the smallest value (minimum) in the range.

You will find the formula above the address of the cell that contains the minimum value in the worksheets. The formula will restore the result as “$ b $ 11“The value $ b $ 11 It refers to the minimum value of the value in the current worksheet.

Look for the cell address of a row of numbers:

Likewise, sales values ​​range from The column c to The column pAll in Row 3. Please refer to the image below.

A formula to find the address of the cell that has the maximum value of columns:

=CELL("address",INDEX(C3:P3,MATCH(MAX(C3:P3),C3:P3,0)))

• Max (C3: P3) – He finds the highest value (maximum) in the range C3: P3.

• Match (Max (C3: P3), C3: P3, 0) :

  • match Looking for the maximum value (calculated by the maximum (C3: P3)) in the range C3: P3.

  • 0 in match The job means that it is looking for an accurate match.

  • match Return the relative position to that maximum value in the range.

  • For example, if the maximum value in the M3 cell (is the third cell in the range C3: P3), 3.

• Index (C3: P3, 3) – Returns a reference to the third cell in the range C3: P3 (It is M3).

• Cell (“Address”, …) The cell address of the cell that contains the maximum value is restored.

The implementation of the above formula will return the value of the cell address that contains the maximum value of different columns. For example, the formula will repeat the cell address.$ M $ 3Which contains the maximum value.

The formula to find the address of the cell that contains the minimum value of the columns:

=CELL("address",INDEX(C3:P3,MATCH(MIN(C3:P3),C3:P3,0)))

• Min (C3: P3): He finds the smallest value (minimum) in the range.

Likewise, the formula will repeat the address of the cell that contains the minimum value of multiple columns. For example, the formula will repeat the cell address.$ J $“Which contains the minimum value of the columns.

Jobs used to achieve this:

That’s it. This tutorial was originally published on how to find that the cell contains the maximum values ​​or minimum values ​​in Excel?