A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup with offset



 
 
Thread Tools Display Modes
  #1  
Old March 20th, 2007, 04:12 PM posted to microsoft.public.excel.worksheet.functions
Mike K
external usenet poster
 
Posts: 33
Default 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  
Old March 20th, 2007, 04:50 PM posted to microsoft.public.excel.worksheet.functions
Billy Liddel
external usenet poster
 
Posts: 489
Default 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  
Old March 20th, 2007, 05:03 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default 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  
Old March 20th, 2007, 05:09 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default 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  
Old March 20th, 2007, 05:11 PM posted to microsoft.public.excel.worksheet.functions
Mike K
external usenet poster
 
Posts: 33
Default 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  
Old March 21st, 2007, 12:44 PM posted to microsoft.public.excel.worksheet.functions
Mike K
external usenet poster
 
Posts: 33
Default 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  
Old March 21st, 2007, 05:20 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default 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






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.