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

stilton1

New Member
Joined
Feb 4, 2019
Messages
6
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.

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


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;">[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[/TR]
</tbody>[/TABLE]
</body>
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
ABCDEFGHIZAA
1JanJanJanJanFebFebFebFebJulJul
2Wk1Wk2Wk3Wk4Wk1Wk2Wk3Wk4Wk1Wk2
3Dep 155124679895623124848
4Dep 212568932548797645487
5Dep 313467998653221549722
6Dep 425583669471425485312
7Dep 566558831649756895152
8
9
101/18/2019
11Jan
12Dep 155124679
13Dep 212568932
14Dep 313467998
15Dep 425583669
16Dep 566558831
Sheet
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top