The references for the ranges need to be absolute. Try...

=MAX(IF($A$1:$A$18=C1,$B$1:$B$18))

and

=MIN(IF($A$1:$A$18=C1,IF($B$1:$B$18<"",$B$1:$B$18 )))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!

In article ,
kwrohde wrote:

That does not work entirely.

here is my example


A B C D
2 1123 1 2000
4 5000 2 4566
5 455456 3 1000000
6 450000 4 45645
8 500 5 456
9 1000 6 643
1 550 7 956
3 4566 8 12
7 956 9 10000
1 2000
2 4566
3 1000000
4 45645
5 456
6 643
7 545
8 12
9 10000


the formula in cell d1 is {=MAXA(IF($A1:$A18=$C1,$B1:$B18))}

If you try this example you will see the answers in cell d5, d6, and d8
are in correct.

the formula {=MAX(--($A1:$A18=$C1)*$B1:$B18)} returns the same result.

I found a different solution in another forum that yields correct
results

the formula is:

{=INDEX($B$1:$B$18,MATCH($C1&"
"&MAX(IF($A$1:$A$18=$C1,$B$1:$B$18,0)),$A$1:$A$18& "
"&$B$1:$B$18,0),0)}

this formula works fine but I also need to find for example "the
minimum value in column B while column A = 7"

I assumed that i could substitute max in the formula above with min but
that returns #NA.

Any other ideas?