You have the monthly sales of forty reps and your boss asks who sold the most and what the runner-up made. You do not want to sort the table or lose the list’s original order.
How to use Max and Min Formula step by step
1. Choose what you are after
The highest, the lowest, the Nth highest or lowest, the highest or lowest meeting a condition, the gap between both or the name tied to the extreme value.
2. Give the range
Type the range of numbers and, if needed, the condition range or the names range. The positions adapt to what you choose.
3. Copy it into your sheet
Paste the formula into a free cell. If the range grows, extend it or turn it into a table so the formula follows.
The Step Count: Traditional Friction vs. KorexTools
Finding the top value by sorting the table (4 steps)
- Sort the table from highest to lowest.
- Read the first name and the first value.
- Undo the sort so the original list is not lost.
- Repeat every time some figure changes.
With the KorexTools “Max and Min Formula” builder (3 steps)
- You choose the extreme you want.
- You give the range and, if needed, the condition.
- You copy the formula and it updates itself when the data changes.
Knowing the highest value is little use if you do not know whose it is
MAX and MIN return the extreme number of a range and ignore text and empty cells. For the second or third value there are LARGE and SMALL, which also ask for the position. With a condition, MAXIFS and MINIFS restrict the search to the rows that qualify, for instance the biggest sale in one region only.
What gets asked for next is the name: who sold the most. It is solved by combining INDEX with MATCH, where MATCH locates the position of the maximum and INDEX returns the name on that row. If two rows tie for the maximum, the formula returns the first one it finds.
Which formula answers what
- •Which is the highest?: MAX, and its partner MIN for the lowest.
- •Which is the second?: LARGE with position 2.
- •Whose is it?: INDEX with MATCH on the maximum.
Sales of 120, 340 and 275 in B2:B4, names in A2:A4
| What you want | Formula | Result |
|---|---|---|
| The highest | =MAX(B2:B4) | 340 |
| The second highest | =LARGE(B2:B4,2) | 275 |
| Who holds the highest | =INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4,0)) | The name on row 3 |
Common Mistakes and How to Avoid Them
- ⚠️Passing a range with text headers and not understanding why they are ignored.
- ⚠️Asking for the Nth largest with a position beyond the number of data points.
Frequently Asked Questions
Does MAXIFS work in my Excel?
Only in Microsoft 365 and Excel 2019 or later. In earlier versions you use a combination of MAX and IF as an array formula.
How do I ignore zeros when looking for the minimum?
With MINIFS and the condition “not equal to zero”. If you lack that function, MIN will return 0 whenever a cell holds that value.
What if two values tie for the maximum?
MAX returns the number and the associated name will be the first row holding it. To see every tie use a filter.
What I would do if I were in your shoes
«If the maximum looks wrong, the problem is almost never the formula: look at the data first.» — DataKorex Engineering Team.