Rolling Spreadsheet Data to 2020 - INDEX/Match?

I have a spreadsheet that is full of hlookups. This spreadsheet reports data results by week. So, the horizontal header is the weeks, i.e. week 1, week 2, week 3...the header for the weeks never changes and as we enter rows for each category of data, i.e. # of days, # of invoices, # of payments, they continue to pull from the particular week number we are reporting on. The first attachment is the "dashboard" that is pulling the data from the "Data" tab (second attachment).

The Rows are years, i.e 2015, 2016, 2017, etc - but the rows are separated by mini headers, i.e. # of days for week 1 in 2015. # of invoices for week 10 in 2017, etc.

Thus, we are using horizonal lookups to pull data for different categories based on week and year.

The "Data" tab (second attachment) has these categories by row and for each category, we have to go in and insert a line for the new year. So, say rows 1-10 are tracking the # of business day in the week by year. Then rows 12-20, are counting the # of employees by week (columns) by year (rows). So you can see there are different sections of data by rows. As we insert lines for each section of data (to insert the new year), it breaks the hlookups. After doing some research, I believe that the Index/Match feature might be my best bet. However, because I'm under a deadline, I don't want to spend the time learning how to roll the data if there is a better method. Can the Index and Match functions work when there are multiple sections of rows that require the new year to be inserted? Keep in mind, the week #'s never change.

If this is the correct solution to roll this spreadsheet, is there a way to easily convert the hlookups to the Index/Match formulas, so that we can pull data based on year and week for all kinds of sections of data, i.e. # of days, # of Employees, # of invoices, # of customers. I appreciate any help anyone can provide. Cheers!






Annotation 2019-12-07 134047 #3.pngAnnotation 2019-12-07 134047.png

Without an actual sample of the worksheet i can't be sure...but
i believe a potential easy fix for going forward would be to add an extra blank row.
So lets userow 13 as an example. You will need to add 2020 now which will be inserted into a new row 14. I would suggest after doing this to add another blank row so now 2020 is in row 14 and you have a blank row 15. Then change your hlookups for that section to include the blank row and lock the references $$. I believe in December 2020 when you go to update to 2021, insert a new row in-between row 14 and 15. This will be your 2021 which will be row 15 and your formulas should already be updated to include this row because they are still going after that same blank row which has now been moved to row 16.

My apologies, this is my first time posting here. Here is an example. You'll see the Dashboard tab and then the Data tab and how the data is pulling into the Dashboard. All of the hidden rows are blank, as they are required to be there or the hlookup will break.

I am unfortunately still struggling to fully follow what you are asking for. It kind of seems like you want to know if Index Match will be more beneficial over an Hlookup. Just by looking at this sheet my guess is that with some changes yes, it could be more beneficial. You may want to consider actually using an =Index( ,Match(), Match()) in order to set it up for years ahead. You could add the current year somewhere on your dashboard and reference that cell in order to look up whichever years data you want from the DATA tab. This way in prepping for the new month all you would have to do is add new year lines and then just change one cell from 2019 to 2020 and it should pull correct information for new year. Again its hard for me to follow exactly what you want.