The easiest way to solve this problem is with the MINIFS function.
However, there are actually several options.
Each approach is explained below.
For convenience, all data is in an Excel Table nameddatain the range B5:C16.
If you are new to Excel Tables,this article provides an overview.
MINIFS function
TheMINIFS functioncan get the minimum value in a range based on one or more criteria.
The reference to E5 is relative and changes at each new row.
The TRUE values correspond to values in the table associated with group A. you might instead provide an array of values created with another operation.
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.
The HSTACK function combinesugroupsandresultshorizontally, and VSTACK combines the header row and the data to make the final table.
The final resultspillsinto multiple cells on the worksheet.
Working from the inside out, theIF functionis evaluated first.
For all other groups, the value is FALSE.
The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
More than one condition can be tested by nesting IF functions.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
Each subsequent array is appended to the right of the previous array….
UNIQUE Function
