lookup tables - Excel: Find min/max values in a column among those matched from another column

Question

I have a table with two columns, say A:B. I have a separate list (in column D) of all different values in column A. For each target value in column D, I want to find, among all rows whose col A matches the target, the minimum and maximum values in column B. For example, if data is as shown,

    col A   col B        col D
 1  7.5     1.00         7.5      1.00   1.20
 2  7.5     1.04         8        1.08   1.45
 3  7.5     1.08         8.5      1.17   1.83
 4  7.5     1.15
 5  7.5     1.20
 6  8       1.08
 7  8       1.13
 8  8       1.20
 9  8       1.29
10  8       1.38
11  8       1.43
12  8       1.45
13  8.5     1.17
14  8.5     1.22
15  8.5     1.26
16  8.5     1.35
17  8.5     1.42
18  8.5     1.51
19  8.5     1.58
20  8.5     1.64
21  8.5     1.69
22  8.5     1.74
23  8.5     1.79
24  8.5     1.83

I want to have formulas that return the last two columns (min and max).

Notes:

1. It would be convenient to have something that works even when referring to ranges going beyond the last row (e.g., using $A$8:$A$50 in formulas, not necessarily $A$8:$A$24), so that new data can be added at the bottom of columns A,B and everything gets updated automatically.

2. Columns A,B will actually contain other data, headers, etc., so I guess some formulas may not work with references to whole columns like $A:$A.

EDIT: I have just found a few similar/related posts

Find MIN/MAX date in a range if it matches criteria of other columns

Conditional Min and Max in Excel 2010

select min value in B column for same values in A columns excel?

Given value in column A, find min/max in column B in EXCEL

find max or min in excel, with conditions

Answer 1

It works for me.

for min:

=MIN(IF(($A$1:$A$50=D1),($B$1:$B$50)))

for max:

=MAX(IF(($A$1:$A$50=D1),($B$1:$B$50)))

Note, that it is an array formulas, so you need to press CTRL+SHIFT+ENTER