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?


--
n_gineer
------------------------------------------------------------------------
n_gineer's Profile: http://www.excelforum.com/member.php...o&userid=29159
View this thread: http://www.excelforum.com/showthread...hreadid=488810

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

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=488810

Thank you for your help. I will give it a shot.


--
n_gineer
------------------------------------------------------------------------
n_gineer's Profile: http://www.excelforum.com/member.php...o&userid=29159
View this thread: http://www.excelforum.com/showthread...hreadid=488810

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


"n_gineer" wrote:


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?


--
n_gineer
------------------------------------------------------------------------
n_gineer's Profile: http://www.excelforum.com/member.php...o&userid=29159
View this thread: http://www.excelforum.com/showthread...hreadid=488810