Vlookup, Match, Index, Nesting If's....

[stilton1]

I've been trying to figure out the best way to get data based on a current date and typically use vlookup but it won't allow 12 conditional statements to look at each month. I want to populate based on the current date retrieving from tables from each month assuming the table was extended out all the way to December in the same format.

	Jan	Jan	Jan	Jan	Feb	Feb	Feb	Feb	Mar	Mar	Mar	Mar	Apr	Apr	Apr	Apr	May	May	May	May	Jun	Jun	Jun	Jun	Jul	Jul	....
	Wk1	Wk2	Wk3	Wk4	Wk1	Wk2	Wk3	Wk4	Wk1	Wk2	Wk3	Wk4	Wk1	Wk2	Wk3	Wk4	Wk1	Wk2	Wk3	Wk4	Wk1	Wk2	Wk3	Wk4	Wk1	Wk2	...
Dep 1	55	12	46	79	89	56	23	12	02	15	48	78	45	56	89	23	56	89	56	12	54	87	35	68	48	48	...
Dep 2	12	56	89	32	54	87	97	64	31	65	32	02	15	59	48	78	55	44	11	22	23	56	89	89	54	87	...
Dep 3	13	46	79	98	65	32	21	54	87	97	64	31	30	62	95	84	15	48	59	26	03	15	45	78	97	22	...
Dep 4	25	58	36	69	47	14	25	48	15	25	36	65	98	32	12	45	65	78	98	85	25	41	36	75	53	12	...
Dep 5	66	55	88	31	64	97	56	89	23	15	59	26	48	97	64	31	13	46	79	82	46	58	57	54	51	52	...

<tbody>
</tbody>

So if it was 1/18/19 I would want to select all values in each department for the month of Jan...or if it was 11/15/19 I would want to select all values in each department for November and fill the table below with that data:

January
Dep 1 55 12 46 79
Dep 2 12 56 89 32
Dep 3 13 46 79 98
Dep 4 25 58 36 69
Dep 5 66 55 88 31


Thanks for any help getting past the nested if limitations with this!

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Jan

Jan

Jan

Jan

<tbody>
</tbody>

</body>

 

[ConPellegrini]

You could try doing something like an OFFSET, where the new array uses the month of the date you're in, and the width and height of the array are COUNTIF(row;month) and COUNTA(column), or even hard code them if you want to. This array would be dynamic, changing in accordance with the month you put in. After that you can use an INDEX and a MATCH for each week/ dept in this array you just created

 

[AhoyNC]

Maybe something like this. Note that your column headers must match the month name (cell A11 must match the headers in row A1 in the example below).
The formula in B12 is an array formula and must be entered with CTRL-SHIFT-ENTER. Then drag the formula down and across as needed. Change ranges to match your data.

Excel Workbook
	A	B	C	D	E	F	G	H	I	Z	AA
1		Jan	Jan	Jan	Jan	Feb	Feb	Feb	Feb	Jul	Jul
2		Wk1	Wk2	Wk3	Wk4	Wk1	Wk2	Wk3	Wk4	Wk1	Wk2
3	Dep 1	55	12	46	79	89	56	23	12	48	48
4	Dep 2	12	56	89	32	54	87	97	64	54	87
5	Dep 3	13	46	79	98	65	32	21	54	97	22
6	Dep 4	25	58	36	69	47	14	25	48	53	12
7	Dep 5	66	55	88	31	64	97	56	89	51	52
8
9
10	1/18/2019
11	Jan
12	Dep 1	55	12	46	79
13	Dep 2	12	56	89	32
14	Dep 3	13	46	79	98
15	Dep 4	25	58	36	69
16	Dep 5	66	55	88	31
Sheet

 

[stilton1]

I wasn't able to get it to work with the actual tables I created, but when I modified it to fit the layout above it worked perfectly, thank you!!! This seems to be much more effective than some of the coding I've put together which is pretty extensive with over 100 different tabs to automate and this shows I really need to get a better understanding of the index function but for the life of me I can't process for some reason. I sincerely appreciate the help and lesson!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.
You may find this link on INDEX / MATCH to be of help.

https://www.youtube.com/watch?v=u-8D2G0y9EM