Originally Posted by
Pete_UK
It's difficult to talk in generalities - I prefer to have actual cell and column references to illustrate the formula. Basically, you would have something like this:
=MAX(IF((criteria1_range = criteria1)*(criteria2_range = criteria2),max_range))
and this should be committed using Ctrl-Shift-Enter rather than the usual Enter.
criteria_range for both criteria would in your sample data be $B$2:$B$5, and criteria1 would be >=100 and criteria2 would be <=150 (no need for the red equals sign above), The actual numbers could be replaced by cell references which contain those numbers, making the formula easier to copy down. The max_range would be $C$2:$C$5 in your example.
Hope this helps.
Pete
Bookmarks