Excel Match Value to a date period in table and return adjacent #

[jwoo6452]

I have daily data on Sheet1 containing many columns including a "period#" column and a "date" column. Sheet2 contains a table of ~monthly periods (1-24 so far) that have variable start-end dates in adjacent columns (three columns: period #, startdate, enddate). I would like to find a function for the "period#" column on Sheet1 that will automatically match the date in the adjacent "date" column with a period number for the date ranges defined on Sheet2. Any suggestions with or without VBA?

 

[PHV]

Take a look at the VLookUp worksheet function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jwoo6452]

I know how Vlookup would work I enter a table of dates and matching period numbers for EACH day on sheet2, but then I may as well assign a period number to the data itself since the number of entries per day is about 10-20(I assign manually now). But, I would like to be able to enter a date period# with start and end date on Sheet2 in adjacent columns and have the PERIOD column on Sheet 1 change dynamically.

Sheet1:
---------A---------B---------C---------D
-------DATE------ITEM-------QTY-----PERIOD
-----01/01/05------10--------300-------1
-----01/02/05------10--------250-------1
-----01/02/05-------3--------100-------1
...
-----02/15/05-------1---------17-------2
...etc.

Sheet2:
------A---------B---------C
----PERIOD----START-------END
------1-----01/01/05----02/23/05
------2-----02/24/05----03/15/05

I have thought that, since the data is in a pivottable, the period couuld be assigned via a calculated field, but the PERIOD is in the pivottable ROW fields, so I believe that a formula in the data table is the easiest way. I just don't know how to combine vlookup and a date range!