Multi Range Arguments

Hello grg

you need to order your table by unit price, small to large (ascending). Then you can use an Index/Match combo to define the range.

Have a look at the attached and inspect the formula in H2 with the Evaluate Formula tool on the Formulas ribbon.

cheers

Or, if you're unable to sort the table:

=MAX(IF(($C$2:$C$7>=30)*($C$2:$C$7<=40),$D$2:$D$7))
confirmed with CTRL + SHIFT + ENTER

(references to 30 and 40 can be replaced with cell references that contain the values of interest)

Yes, use INDEX and MATCH the MAX against the % Column, ie:

=INDEX($A$2:$A$7,MATCH(MAX(IF(($C$2:$C$7>=30)*($C$2:$C$7<=40),$D$2:$D$7)),$D$2:$D$7,0))
confirmed with CTRL + SHIFT + ENTER

(if you're storing the MAX % in another cell already then don't repeat the calculation - use the cell containing the MAX% as the criteria in the MATCH - at which point the need for Array entry is removed)

In which case I suspect something has been transferred incorrectly.

Post a sample.

You have issues in so far as G1981 contains an underlying error (#DIV/0!) - this can be accounted for but I will assume for sake of demo this is a typo / oversight.

If we assume you want to return Desc1 associated with Max % where price between $6 and $7 such that J1 holds 6 and K1 7 then:

Please Login or Register  to view this content.

confirmed with CTRL + SHIFT + ENTER