Lookup with offset

[Mike K]

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

 

[Billy Liddel]

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

 

[RagDyeR]

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!
=====================================================

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

 

[RagDyeR]

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 !
-----------------------------------------------------------------------------------------------

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!
=====================================================

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 K]

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 K]

I only needed 5 rows. I have adjusted accordingly and am getting the desired
results.

Many Thanks,

Mike

 

[RagDyeR]

You're welcome, and appreciate the feed-back.
--

Regards,

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