Hi, I am new here. Thank you for taking the time to read and propose a solution.
I have large set of data (thousands of rows) that contains a column with dates and a column with sales data. I need to set up multiple tables that show sales data from date ranges, each table centered around a specified date. So the date at the top of each table determines the sales data that goes into one cell in the table, and all of the other cells in that same table should pull sales data from the sales just before that date to the sales just after that date. For example, if the date in the table says "January 20, 2017," that table should populate with the sales data from the three sales before January 20th to the three sales after January 20th.
I know how to use VLOOKUP to pull the sales data for January 20th, but what I don't know how to do is pull the data from (for example) three sales before that (three rows above it) down to three sales days after that date. On some dates there were no sales, so the dates are not always contiguous; which means I cannot simply do a VLOOKUP to pull in data from January 19th, since there may not have been any sales on that day.
I also know how to use OFFSET to pull data 3 rows up from a specified cell. But I cannot find any way to combine the functions of VLOOKUP and OFFSET to look up the data for January 20th and then look 3 rows above it.
Here is an example of what the data might look like and the table that I want to create from selected data within the data set.
Workbook5.jpg
So I should get a table with the Units automatically filled in with 52, 59, 62, 44, 47, 53, and 51.
Bookmarks