Vlookup max value only

N

n_gineer

I am doing a lookup from one workbook to another and there are multiple
rows that match the lookup value. I need it to return the highest value
only from the second column. Suggestions?
 
V

vezerid

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
 
S

SteveG

This seemed to work for me.


{=MAX(IF($B$2:$B$5="Test",$C$2:$C$5,0))}

Column B is where "Test" would be found and column C is where your
corresponding values are.

Commit with Ctrl-Shift-Enter as this is an array formula.

Cheers,

Steve
 
A

Ashish Mathur

Hi,

You may try this array formula (Ctrl+Shift+Enter). This is in range A1:B7

Ashish 100
Sanjay 200
Pongal 300
Ashish 400
Rajesh 500
Suresh 600
Ashish 700

Enter Ashish (below) in cell A10.

Ashish

In cell B10, enter the follwoing array formula

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

Copy this formula down. You will now have multiple occurences of numbers
against the name Ashish. You may now use the max function
 
Top