 |
 | 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 / March 2007Lookup with offset
Oh Wise Ones, I have week ending dates in B6:F6. In F1 I have the formula =LOOKUP(NOW(),B6:F6) which finds the correct date in D6. In G2 If I have =OFFSET(D6,2,0) I get the value from D8- it works. Why doesn't =LOOKUP(INDIRECT(F1),2,0) work. I get a REF error. I need to find the last week ending date from that range and get the values from that column. Any help would be much appreciated. Thanks, Mike Mike F1 has to have a cell reference, hence the REF error. What is in F1? If A5 contains Art and B10 contains A5 the indirect(B10 returns Art. Regards Peter > Oh Wise Ones, > [quoted text clipped - 6 lines] > > Thanks, Mike Billy, F1 contains the "=LOOKUP(NOW(),B6:F6)" I was trying to make it a 2-step process to make it easier. I need to do this: Find the date in B6:F6 closest to but less than Now or Today Go down 2 rows in the corresponding column and get the value. Mike > Mike > [quoted text clipped - 15 lines] > > > > Thanks, Mike With your datalist going down, say 15 rows under the date row, And you want to return the 2nd row, Is this what you're looking for: =INDEX(B7:F16,2,MATCH(LOOKUP(NOW(),B6:F6),B6:F6,0)) You could easily assign a cell to contain the row number (of the datalist - *not* the sheet row) that you're looking to return. Sat you enter the row number to return into A1, then the formula looks like this: =INDEX(B7:F16,A1,MATCH(LOOKUP(NOW(),B6:F6),B6:F6,0))  SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Oh Wise Ones, I have week ending dates in B6:F6. In F1 I have the formula =LOOKUP(NOW(),B6:F6) which finds the correct date in D6. In G2 If I have =OFFSET(D6,2,0) I get the value from D8- it works. Why doesn't =LOOKUP(INDIRECT(F1),2,0) work. I get a REF error. I need to find the last week ending date from that range and get the values from that column. Any help would be much appreciated. Thanks, Mike Actually, my formula is for a datalist going down *10 rows* below the date row. Adjust the ranges to fit your situation. SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- With your datalist going down, say 15 rows under the date row, And you want to return the 2nd row, Is this what you're looking for: =INDEX(B7:F16,2,MATCH(LOOKUP(NOW(),B6:F6),B6:F6,0)) You could easily assign a cell to contain the row number (of the datalist - *not* the sheet row) that you're looking to return. Sat you enter the row number to return into A1, then the formula looks like this: =INDEX(B7:F16,A1,MATCH(LOOKUP(NOW(),B6:F6),B6:F6,0)) SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Oh Wise Ones, I have week ending dates in B6:F6. In F1 I have the formula =LOOKUP(NOW(),B6:F6) which finds the correct date in D6. In G2 If I have =OFFSET(D6,2,0) I get the value from D8- it works. Why doesn't =LOOKUP(INDIRECT(F1),2,0) work. I get a REF error. I need to find the last week ending date from that range and get the values from that column. Any help would be much appreciated. Thanks, Mike I only needed 5 rows. I have adjusted accordingly and am getting the desired results. Many Thanks, Mike > Actually, my formula is for a datalist going down *10 rows* below the date > row. [quoted text clipped - 23 lines] > > Thanks, Mike You're welcome, and appreciate the feed-back. SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- I only needed 5 rows. I have adjusted accordingly and am getting the desired results. Many Thanks, Mike "RagDyeR" wrote: > Actually, my formula is for a datalist going down *10 rows* below the date > row. [quoted text clipped - 10 lines] > > Thanks, Mike |
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.