Explanation
In this example, the goal is to get the minimum value for each group in the data as shown. The easiest way to solve this problem is with the MINIFS function. However, there are actually several options. If you need more flexibility (you need to work with arrays instead of ranges), you can use the MIN function with the FILTER function. To create a dynamic summary table with a single all-in-one formula, you can use the BYROW function. In older versions of Excel without the MINIFS function, you can use an array formula based on the MIN function and the IF function. Each approach is explained below. For convenience, all data is in an Excel Table named data in the range B5:C16. If you are new to Excel Tables, this article provides an overview .
MINIFS function
The MINIFS function can get the minimum value in a range based on one or more criteria. The generic syntax for MINIFS with a single condition looks like this:
=MINIFS(min_range,range1,criteria1)
Note that the condition is defined with two arguments : range1 and criteria1 . In this problem, the condition is that the group value in column B must equal the group value in column E. We start off with the min_range , which is the Value column:
=MINIFS(data[Value],
Next, we add the criteria range, which is the Group column:
=MINIFS(data[Value],data[Group],
Finally, we add the criteria itself, which comes from cell E5:
=MINIFS(data[Value],data[Group],E5)
As the formula is copied down, the table references behave like absolute references and don’t change. The reference to E5 is relative and changes at each new row. The result is the minimum value for each group listed in column E.
The MINIFS function is a straightforward solution that works well. However, it does have one significant limitation: the range arguments inside MINIFS must be actual ranges, you can’t substitute arrays. If you need to use arrays, see the MIN + FILTER option, or the all-in-one summary table formula based on the BYROW function below.
MIN + FILTER
In the dynamic array version of Excel, another way to solve this problem is with the MIN function and the FILTER function like this:
=MIN(FILTER(data[Value],data[Group]=E5))
Inside the MIN function, the FILTER function is configured to filter values by group:
FILTER(data[Value],data[Group]=E5)
Array is provided as the Value column in the table, and the include argument is a simple expression:
data[Group]=E5
With “A” in cell E5, the expression above returns an array like this:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
Because there are 12 values in Group, there are 12 TRUE and FALSE results. The TRUE values correspond to values in the table associated with group A. With the array above acting as a filter, the FILTER function returns an array that contains the four values in group A directly to the MIN function:
=MIN({81;131;127;140})
MIN then returns a final result of 81. The primary advantage of using the MIN function with the FILTER function is that you are not required to supply a range of values on the worksheet. You can instead provide an array of values created with another operation. This is very useful when the source data needs to be manipulated in some way before a min value is calculated.
Summary table with BYROW
In the latest version of Excel, which supports dynamic array formulas , you can create a single all-in-one formula that builds the entire summary table, including headers, like this:
=LET(
groups,data[Group],
values,data[Value],
ugroups,UNIQUE(groups),
results,BYROW(ugroups,LAMBDA(r,MIN(FILTER(values,groups=r)))),
VSTACK({"Group","Min"},HSTACK(ugroups, results))
)
The LET function is used to assign four intermediate variables: groups , values, ugroups , and results . Both groups and values are simple assignments to rows in the table:
groups,data[Group],
values,data[Value],
This is done primarily to make the formula more portable. Defining these variables here keeps all of the worksheet references at the top of the formula where they can be easily changed.
In our summary table, we want a list of unique groups, so we define ugroups (unique groups) with the UNIQUE function like this:
ugroups,UNIQUE(groups), // get unique groups
From the 12 rows in the data, the UNIQUE function returns just 3 unique groups:
{"A";"B";"C"} // unique groups
Note: you could run groups through the SORT function to ensure that groups are in the correct order. In this case the source data already shows groups in order, so it is not necessary.
At this point, we are ready to calculate the minimum values for each group. We do this with the BYROW function which uses a custom LAMBDA function to calculate the minimum values and assigns the result to the results variable:
results,BYROW(ugroups,LAMBDA(r,MIN(FILTER(values,groups=r))))
BYROW runs through the ugroups values row by row. At each row, it applies this calculation:
LAMBDA(r,MIN(FILTER(values,groups=r)))
The value r is the unique group in the “current” row in the summary table. Inside the MIN function, the FILTER function is configured to filter values by group like this:
FILTER(values,groups=r)
In the first row (group A), the result from FILTER is an array like this:
{81;131;127;140}
This array is delivered directly to the MIN function, which returns the minimum number. When BYROW is finished, we have an array with 3 numbers (one for each group) like this:
{81;97;82} // results
This array is the value assigned to results . Finally the HSTACK and VSTACK functions are used to assemble a complete table:
VSTACK({"Group","Min"},HSTACK(ugroups, results))
At the top of the table, the array constant {“Group”,“Min”} creates a header row. The HSTACK function combines ugroups and results horizontally, and VSTACK combines the header row and the data to make the final table. The final result spills into multiple cells on the worksheet.
MIN + IF
In older versions of Excel that do not have the MINIFS function, you can solve this problem with an array formula based on the MIN function and the IF function like this:
=MIN(IF(data[Group]=E5,data[Value]))
Note: this is an array formula and must be entered with control + shift + enter in Legacy Excel .
Working from the inside out, the IF function is evaluated first. The logical test is an expression that tests the entire Group column against the value in cell E5:
=IF(data[Group]=E5 // logical test
Because there are 12 values in data[Group] , the result is an array with 12 TRUE / FALSE values like this:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
The TRUE values correspond to rows where the group is “A”. For all other groups, the value is FALSE. For value_if_true , we supply the Value column, and we omit value_if_false altogether:
IF(data[Group]=E5,data[Value])
The final result from IF is an array like this:
{81;FALSE;FALSE;131;FALSE;FALSE;127;FALSE;FALSE;140;FALSE;FALSE}
Looking at the values in this array, you can see that the IF function acts like a filter. Only numbers associated with group “A” make it through the filter, and other values are replaced with FALSE. The IF function delivers this array directly to the MIN function :
=MIN({81;FALSE;FALSE;131;FALSE;FALSE;127;FALSE;FALSE;140;FALSE;FALSE})
The MIN function automatically ignores FALSE values and returns the minimum number in the array: 81.
Explanation
In a nutshell, this formula uses the LARGE function to find the nth largest value in a set of data. Once we have that value, we plug it into a standard INDEX and MATCH formula to retrieve the associated name. In other words, we use the nth largest value like a “key” to retrieve associated information.
The LARGE function is a straightforward way to get the nth largest value in a range. Simply provide a range for the first argument ( array ), and a value for n as the second argument ( k ):
=LARGE(range,1) // 1st largest
=LARGE(range,2) // 2nd largest
=LARGE(range,3) // 3rd largest
Working from the inside out, the first step is to get the “1st” largest value in the data with the LARGE function:
LARGE(score,F5) // returns 93
In this case, the value in F5 is 1, so we are asking for the 1st largest score (i.e. the top score), which is 93. We can now simplify the formula to:
=INDEX(name,MATCH(93,score,0))
Inside the INDEX function , the MATCH function is set up to locate the position of 93 in the named range score (D5:D16):
MATCH(93,score,0) // returns 3
Since 93 appears in the 3rd row, MATCH returns 3 directly to INDEX as the row number, with name as array:
=INDEX(name,3) // Hannah
Finally, the INDEX function returns the name in the 3rd row, “Hannah”.
Notice we are picking up the values for n from the range F5:F7, in order to get the 1st, 2nd, and 3rd highest scores as the formula is copied down.
Retrieve group
The same basic formula will work to retrieve any associated information. To get the group for the largest values, you can simply change the array supplied to INDEX with the named range group :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
With the value 1 in F5, LARGE will get the highest score, and the formula will return “A”.
Note: with Excel 365 , you can use the FILTER function to list top or bottom results dynamically .
With XLOOKUP
The XLOOKUP function can also be used to return the name of the nth largest value like this:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE returns the largest value, 93, directly to XLOOKUP as the lookup value:
=XLOOKUP(93,score,name) // Hannah
With the named range score (D5:D16) as the lookup array , and name (B5:B16) as the return array , XLOOKUP returns “Hannah” as before.
Handling ties
Duplicate values in the numeric data will create a “tie”. If a tie occurs in the values being ranked, for example, if the first and second largest values are the same, LARGE will return the same value for each. When this value is passed into the MATCH function, MATCH will return the position of the first match, so you will see the same (first) name returned.
If there is the possibility of ties, you may want to implement some kind of tie-breaking strategy. One approach is to create a new helper column of values which have been adjusted to break ties . Then use the helper column values to rank and retrieve information. This makes the logic used to break ties clear and explicit.
Another approach is to break ties based on position only (i.e. the first tie “wins”). Here is a formula that takes that approach:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365 .
Here, we use MATCH to find the number 1, and we construct a lookup array using boolean logic that (1) compares all scores to the value returned by LARGE:
score=LARGE(score,F5)
and (2) uses an expanding range check if the name is already in the ranked list:
COUNTIF(H$4:H4,name)=0
When a name is already in the list, it is “cancelled” by the logic, and the next (duplicate) value is matched. Notice the expanding range starts on the prior row , in order to avoid a circular reference.
This approach works in this example because there are no duplicate names in the name column. However, if duplicate names occur in ranked values, the approach needs to be adjusted. The easiest solution is to make sure that names are unique.
Notes
- To get the name of nth value with criteria , (i.e. limit results to group A or B) you will need to extend the formula to use additional logic .
- In Excel 365 , the FILTER function is a better way to list top or bottom results dynamically . This approach will automatically handle ties.
