Lookup for moving months

Hello. I'm creating a table that will include a column with numbers for the current year/month as well as a column for prior year/month. In the rows I have different categories. I want to link this table to a tab that has the row headings (for the table) in the left column and the months going across the columns. I could just manually select the cells that I want to include but I want the numbers to update based on the month. So, if I April in a cell, it will automatically grab the data from the year/month (the tab has the dates along the top row). How can I do this? Various methods welcome. Thanks!!!!

Hi


Based on your request, try looking at the MATCH and INDEX functions.


If you want something more specific to your situation, then attach an example file.

rylo

Ok thanks. File attached. If you could give various methods of accomplishing it, I would appreciate it. Yeahaaa!!

Hi

Based on your example file
table!B5: =OFFSET(Data!$A$1,ROW()-2,MATCH($B$1,Data!$2:$2,0)-1)

Do you really need to have multiple methods of getting this result?

rylo

I really do!! The more the merrier, right Robin Hood?

Also, if you add rows into the first tab, that formula doesn't work No good!!!

Hi

OK, adding rows to the top of the data was not mentioned as a possibility in your original post.

Here's another couple of options, again for Table!B5
=OFFSET(Data!$A$1,MATCH(A5,Data!A:A,0)-1,MATCH($B$1,Data!$2:$2,0)-1)
=INDEX(Data!1:20,MATCH(Table!A5,Data!A:A,0),MATCH(Table!B1,Data!2:2,0))

For other options, do you want to go down the track of macro solutions that you have to run manually, user defined functions or what?

Again, there are some expectations of the stability in your data. So if you are not likely to have any stability in structure, and you give advice on what changes could take place, and exactly what parts are likely to remain consistent.

rylo

That's more like it Rylo. Good stuff!!