Lookup in a Date Range

[cmajka]

Hi All,

I'm trying to find a way to look in the dataset and if the date falls within a certain date range, then I want to return that corresponding value. In column A, I have set up the beginning date, for example 3/31/2013. In Column B, I have set up the ending date, i.e., 4/06/2013. So, if a value in the dataset falls within that date range, then return the value.

Thanks

C

 

[c_m_s_jr]

Not sure what you plan to do with the data after you get it, but take a look at the advanced filter function.

 

[cmajka]

Let's say I have 12 weeks worth of data. Each week of data needs to be separated onto it's own tab. Ultimately, I want to split one large dataset into 12 small datasets by date range. This dataset will be updated each week so doing it manually is not something that's feasible. If i can just plop the new dataset into a tab, and have the other 12 tabs update automatically with their respective week's worth of data.

 

[Aladin Akyurek]

Let's say I have 12 weeks worth of data. Each week of data needs to be separated onto it's own tab. Ultimately, I want to split one large dataset into 12 small datasets by date range. This dataset will be updated each week so doing it manually is not something that's feasible. If i can just plop the new dataset into a tab, and have the other 12 tabs update automatically with their respective week's worth of data.

Such should be done with code in VBA, although it's also doable with formulas. Try to adapt from post #3 of:

http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

for a formula set up.

 

[Jordan213]

I was trying to do the exact same thing. I tried a lot of different methods, and I discovered that it's a huge pain in the buns. My simplistic but highly effective solution was to construct a table that had every day in the range listed, with the period reference number for every day right next to it. Use a lookup to append the period reference to the appropriate rows in your raw dataset. Lookup against the period reference number instead of the date, voila. I'm sure there's a more sophisticated and complex way of doing it, but is it honestly worth the time? I don't think so.