VLOOKUP with OFFSET

All,

I need your help. I need to do a calculation using dates by week. Specifically, beginning date and end date, i.e. Beg - 1/12/14 end 1/18/14 - Week 1 and so on... That said, I have to use a VLOOKUP function with the 4th argument as TRUE. This will return a VALUE. OFFSET will only work if I am referencing a CELL (not a VALUE). Does anyone know how to reference a CELL within an array??

My function would ideally look something like this...

=OFFSET(VLOOKUP($D4,$E$4:$E$100,2,TRUE),0,52) however this does not work for the reason stated above.


Thanks to all!

If you're offsettig by 'Columns', that's already built into the vlookup itself, the 3rd argument 'Colindex'.
So you'd want to change the 2 to 54
But also need to adjust the range to be 54 columns wide.

Try
=VLOOKUP($D4,$E$4:$BF$100,54,TRUE)

I can't simply change the columns. I am working with about 2200 dates and each date will fall within a certain week. I need to find the beginning week and then find the week a year later....

The only thing I can think of is a vlookup with a true statement (it will not be an exact match), then offset that week to add 52 and return the corresponding week.


Thanks again

Well as you've discovered you can't offset from a vlookup.
Because it returns a value, not a Cell/Range.

But you CAN offset from an Index/Match.

So given your original vlookup
VLOOKUP($D4,$E$4:$E$100,2,TRUE)

An equivelent Index/Match to that is
=INDEX($F$4:$F$100,MATCH($D4,$E$4:$E$100,0))

Then you can offset from that
=OFFSET(INDEX($F$4:$F$100,MATCH($D4,$E$4:$E$100,0)),0,52)

The issue with the index match is that my value is approx. It won't be an exact match. The three options with match is exact, greater than, less than. I would need an greater/less than or equal...

Sorry, missed the part that your original vlookup had TRUE...

Change the 0 in the match to 1 (or omit it)
=OFFSET(INDEX($F$4:$F$100,MATCH($D4,$E$4:$E$100)),0,52)