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