Return a array

[MoonBlade]

Hey,

I need a function that can do the following:
I have two culoms of numbers temp and pressure.
I want to find the max pressure for each temp.
I want to do some thing like this:
A B
1 200 3
2 200 4
3 300 1
4 300 5

=Max(?????(a1:a4=200,b1:b4))
So ????? returns the array b1:b2
It should also work in situation like this.

A B
1 200 3
2 200 4
3 300 1
4 200 5

?????(a1:a4=200;b1:b4) returns b1,b2,b4
giving Max(?????(a1:a4=200;b1:b4))=5

 

[Weaver]

try:

=Max(IF(a1:a4=200,b1:b4))

BUT

You have to register the function by pressing ctrl+shift+enter, rather than just enter

This creates an array formula and should deliver the result you're after.

It should look like:

{=MAX(IF(A1:A4=200,B1:B4))}

in the formula bar.