| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup with offset
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 "Mike K" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup with offset
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 )) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Mike K" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup with offset
Actually, my formula is for a datalist going down *10 rows* below the date
row. Adjust the ranges to fit your situation. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "RagDyeR" wrote in message ... 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 )) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Mike K" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup with offset
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 "Billy Liddel" wrote: 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 "Mike K" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup with offset
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. Adjust the ranges to fit your situation. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "RagDyeR" wrote in message ... 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 )) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Mike K" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup with offset
You're welcome, and appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Mike K" wrote in message ... 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. Adjust the ranges to fit your situation. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "RagDyeR" wrote in message ... 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 )) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Mike K" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup and offset | Excel Discussion (Misc queries) | |||
| Offset Type Lookup | Excel Worksheet Functions | |||
| Lookup and offset | Excel Discussion (Misc queries) | |||
| Help with Offset/Lookup | Excel Worksheet Functions | |||
| lookup with offset? | Excel Worksheet Functions | |||
Linear Mode
