I'm trying to create a "Rollup" sheet with information from 5 other TABs. The TAB data is based on (for the example below) being in a TAB named AG (which is in A5). So in TAB AG cell ref F5 is what I would like Vlookup to return. Here's where it get interesting. My Boss has added a drop down box with the beginning dates of a two week period which allows a user to pick (its cell link is in D42) 12/29/2014 which = 1, or Jan/12/2015 which =2 etc.. So what he is asking is:

If the user picks 12/29/2014 from the drop down list, the Vllookup formula returns from TAB AG F5,

If the user picks Jan/12/2015 from the drop down list, the Vllookup formula returns from TAB AG V5,

....and so on and so on. Any thoughts on using Vlookup/Offset or perhaps INDIRECT...
ColA B C D E F
AG J2 L Don AM SHIFT F5 is where I would like the formula