 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008Array Formula - Offset Result Problem
The following Array formula retulrns the maximum value in column E where the value in Column H is Blue {=MAX(($E$2:$E$115)*($H$2:$H$115="Blue"))} How would I return the text in Column B for which is on the same line as the maximum value in column E where the value in Column H is Blue I'm using Excel 2003 TIA A One way =INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E$115)*($H$2:$H$115="Blue"))&"Blue",$E$2:$E$115&$H$2:$H$115,0)) array entered > The following Array formula retulrns the maximum value in column E > where the value in Column H is Blue [quoted text clipped - 9 lines] > > A Vlad, It's still an array =INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E$115)*($H$2:$H$115="Blue")),$E$2:$E$115,FALSE),1) Mike > The following Array formula retulrns the maximum value in column E > where the value in Column H is Blue [quoted text clipped - 9 lines] > > A you would get an incorrect result if the data were: 2 red 5 red 5 blue 4 green where another color happens to have a number that equals blues max and appears before blue in the list. > Vlad, > [quoted text clipped - 17 lines] > > > > A Your right, forgot about duplicates, thanks for the correction Mike > you would get an incorrect result if the data were: > [quoted text clipped - 27 lines] > > > > > > A > you would get an incorrect result if the data were: > [quoted text clipped - 5 lines] > where another color happens to have a number that equals blues max and > appears before blue in the list. Thanks for the suggestion but they don't work for me as it is quite likely that there would be duplicate items with the same max value. I thought something like {=MAX(($E$2:$E$115)*($H$2:$H$115=$J4)*($B$2:$B $115))} would work but it doesn't. Any other suggestions? TIA why did my first suggestion not work????? > > you would get an incorrect result if the data were: > > [quoted text clipped - 15 lines] > > TIA I put the following data into a test sheet:- 1 COL B COL E COL H 2 Test 1 2 red 3 Test 2 5 red 4 Test 3 5 blue 5 Test 4 4 green When I put the array forumla =INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E $115)*($H$2:$H$115="Blue")),$E$2:$E$115,FALSE),1) into cell L2 it returns the test Test 2 whereas I was expecting Test 3. I have uploaded the file to http://www.mediafire.com/?vglvvl3jtn0 - not sure whether you would be happy to open it though but it's there if you want. TIA Andy that was not my suggestion. my suggestion was =INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E$115)*($H$2:$H$115="Blue"))&"Blue",$E$2:$E$115&$H$2:$H$115,0)) array entered - which returns Test 3 for me. > I put the following data into a test sheet:- > [quoted text clipped - 15 lines] > > Andy That works like a treat - thanks for both of your help and suggestions |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.