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?
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?
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
Thank you for your help. I will give it a shot.
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
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks