 |
 | 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 / January 2008index and match closest
example data: > > a1 b1 c1 d1 e1 f1 > > S-213A 10/19/2006 60. 10/20/2006 S-213A > > S-213A 11/3/2006 66. 1/2/2007 S-200A > > S-200A 11/4/2006 50. . > > S-200A 12/3/2006 69. . > > > > I need help with formula in f2 that would: > > Find S-213A in columnA, then match CLOSEST date from D2 with ColumnB, then > > return value in column C. So in this case f2=60 and f3=69. > > THANKS! Try this array formula**: =INDEX(C$2:C$5,MATCH(1,(A$2:A$5=E2)*(ABS(B$2:B$5-D2)=MIN(IF(A$2:A$5=E2,ABS(B$2:B$5-D2)))),0)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)  SignatureBiff Microsoft Excel MVP > example data: >> > a1 b1 c1 d1 e1 f1 [quoted text clipped - 8 lines] >> > return value in column C. So in this case f2=60 and f3=69. >> > THANKS! T. Valko- Thank you so much. This is working. I have a change. Instead of "closest" date I need the closest date but not greater than. I modified my example a bit to explain. > Try this array formula**: > [quoted text clipped - 17 lines] > >> > return value in column C. So in this case f2=60 and f3=50. > >> > THANKS! >I need the closest date but not greater than. If there is no date that meets the condition then the formula will return a #N/A error. Try this array formula** : =INDEX(C$2:C$5,MATCH(1,--(B$2:B$5=MAX(IF((A$2:A$5=E2)*(B$2:B$5<D2),B$2:B$5))),0)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) SignatureBiff Microsoft Excel MVP > T. Valko- > Thank you so much. This is working. I have a change. Instead of [quoted text clipped - 25 lines] >> >> > return value in column C. So in this case f2=60 and f3=50. >> >> > THANKS! |
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.