Create a formula that returns a minimum date from a range based on cell values in another column

Question

I am looking for a formula that will return the earliest date from a column, based on the contents of values in other cells. (Actually I want a Min and Max date, but am assuming the Max will be identical to any Min solution )I know I can return the date I want just by using MIN and specifying the range of cells I want, but I ideally want the formula to be dynamic. I have looked around and believe I possibly need to use a combination of index and match, but cant find any examples that use Min and Max. I have considered using dynamic named ranges to define my task groups, but that would mean having to define a static number of task groups, and there could be many task groups. The sheet below shos some sample date on the left of the workbook, with the summary data on the right. The "hidden worker column" was an idea I had that I though might make the solution easier. So I want the summary data on the right to use either column A, or column B if its easier, to display the min and max dates based on the section number in column F - Is this possible without VBA?

Answer 1

@mthierer's link is good. If you wanted to remove the need to add a "helper column", you could try (data in A1:C10; summary table in E1:G2):

{=MIN(IF(ROUNDDOWN($A$1:$A$10, 0)=$E1, $B$1:$B$10))} (or {=MAX(...)} with $C$1:$C$10)

Note that you have to enter the formula as an array formula with CtrlShiftEnter.

Data (A1:C10):

1       23     57
1.1     42     91
1.2     35     100
1.3     39     80
1.4     28     51
1.5     30     96
2       33     52
2.1     11     73
2.2     48     80
2.3     16     59

Summary Results (E1:G2):

1     23     100
2     11     80