Vlookup max value only
Assuming table occupies cells A1:C10. A:A contains the lookup criterion
(multiple occurences). B:B contains the secondary criterion (we want
the max of). C:C contains the value to be retrieved. Assuming lookup
value is in D5.
The following array formula (enterd with Shift + Ctrl + Enter) will do.
=INDEX($C$1:$C$10,MATCH(D5&" "&MAX(IF($A$1:$A$10=D5,$B$1:$B$10,0)),
$A$1:$A$10&" "&$B$1:$B$10,0))
Note: in the sub-expression: MAX(IF($A$1:$A$10=D5,$B$1:$B$10,0)) use a
number less than any of the numbers expected to appear in column B:B. 0
will do if all numbers are expected to be positive. -(10^307) will do
if no assumptions.
HTH
Kostis Vezerides
|