I am stuck here and I think I am making this more difficult than it is so I am hoping one of you may have a fresh idea.



I have this table above and here is what I want to do:

Based upon the current date - the formula will find the column of the prior week and return the value of that column 5 rows down.
I was trying to think of a way to do it with hlookup but I just couldn’t figure out what to do about the range.

For example:

Todays date is October 16th, 2007 - so the formula would identify the column before where todays date falls in between that range and return the 5th item down.
The value it should return would be 2.

Any ideas? I am stuck. Like I said before - I think I am making this a lot more difficult than it needs to be.

10/16/2007 11:39:44 AM

the 4th argument to h/vlookup is "range lookup". By default it is TRUE, so if the lookup does not find a match for your lookup value, it will match the range the value is in, based on the lower value.

all you have to do is do a normal lookup with the date you want to lookup, then the week-starting-date as the 1st row in the lookup table, and make sure your 4th arguement is TRUE. It will automatically figure out which week the date is in (actually, it doesn't care which "week", it's just looking for the next range).

10/16/2007 12:24:55 PM

thank you sir

10/16/2007 1:56:16 PM