 |
 | 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 / Worksheet Functions / June 2007Looking up 3 cells based on defined value in a row/column location
I have this data. The 17894 in the last row( C17) is the max of C3:F16. That value is located in D16. Is there a way that I can have a cell produce these results - the # in row 2 ( 101,102, 103 or 104), and the day and date in the A & B column to indicate the max value in this case (17894) is from --->>> #102 on Wed 6/13 ? A B C D E F 101 102 103 104 Thu 5/31/2007 17485 12375 14821 9613 Fri 6/1/2007 17533 13059 14090 11798 Sat 6/2/2007 11549 14634 Sun 6/3/2007 17106 16079 13112 Mon 6/4/2007 16529 15759 16333 11113 Tue 6/5/2007 15756 15968 12519 14690 Wed 6/6/2007 17214 13197 14304 7644 Thu 6/7/2007 16957 14613 15106 12105 Fri 6/8/2007 14951 10369 14718 5468 Sat 6/9/2007 15027 14302 13777 Sun 6/10/2007 17861 14707 15678 9403 Mon 6/11/2007 17155 14791 10892 10225 Tue 6/12/2007 15942 15021 15156 14240 Wed 6/13/2007 17489 17894 13917 15599 17894 Thanks, Steve Try these: B3:B16 = date C3:F16 = rng C17 = a formula (as per your description): =MAX(rng) Assume you want the date in cell B20. For the date use this array formula**: =INDEX(Date,MATCH(TRUE,MMULT(--(rng=C17),TRANSPOSE(COLUMN(rng)^0))>0,0)) For the column header (dependent upon the date formula cell B20): =INDEX(C2:F2,MATCH(C17,INDEX(rng,MATCH(B20,Date,0),),0)) For the weekday (dependent upon the date formula cell B20): =TEXT(B20,"ddd") Biff >I have this data. The 17894 in the last row( C17) is the max of C3:F16. >That [quoted text clipped - 28 lines] > > Steve I forgot something: >For the date use this array formula**: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff > Try these: > [quoted text clipped - 49 lines] >> >> Steve Perfect solution. Thanks so much. Steve > I forgot something: > [quoted text clipped - 58 lines] > >> > >> Steve You're welcome. Thanks for the feedback! Biff > Perfect solution. > Thanks so much. [quoted text clipped - 65 lines] >> >> >> >> Steve |
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.