Stuck on this...find MAX

[theta]

Hi

I have data in 3 columns, but need to find the max of one based on certain criteria

COLUMN A contains numbers
COLUMN B contains type
COLUMN C contains location

So, for example, I need a formula in cell A20 that will be the MAX of column A above it, but only where

COLUMN B = "Contract"

AND

COLUMN C = "Amazingstoke"

Not sure on the best approach, using MAX or LARGE ? with either SUMPRODUCT or INDEX/MATCH....or maybe another method im not aware of?

 

[theta]

I am using this one at present ;

It always finds the highest...how would I ammend it to find the lowest, replace the 2 with a 0?

=IFERROR(LOOKUP(2,1/(($A$10:$A15=$A16)/($D$10:$D15=$D16)/($G$10:$G15=$G16)),$B$10:$B15)+1,1)

Just learning

 

[theta]

Hmmm the zero doesnt work

How would I find the minimum value using the same approach (or alternative if the above is not possible)

?

Thanks

 

[Aladin Akyurek]

Hmmm the zero doesnt work

How would I find the minimum value using the same approach (or alternative if the above is not possible)

?

Thanks

Why don't you just replace MAX with MIN in the formula already proposed or DMAX with DMIN?

 

[theta]

Hi Guys

This is all great, thanks

You mentioned using SQL...how would I use SQL within Excel?

Thanks