Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup max value only
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup max value only
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup max value only
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup max value only
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |