VLOOKUP with the ability to select named range based on a date in another cell

I have a heavy duy reopt I am creating with an abundance of historical data. The dates are not "static" so as time progresses these dates on the report to pull in needed data will change. I was initially doing a VLOOKUP to get the values I needed, then I realized that I have historical data, so it seemed more suiting to come up with an alternative that can grow as the report does

I created a Table that lists the Year as the main criteria, based on this, I would like it to select a specific table for the VLOOKUP, which would have a named range, for example...

cell C20 - 8/1/2015
Cell D20 - 2015

Based on cell D20 (2015) I would like to use my HistoricalTable to determine which of the current years data I have on file I need to use, based on 2015, the named range (2nd column) would be data_2015. Then the named range of data_2015 would be used. Based on the date in cell C20, the VLOOKUP for the named ramnge (data_2015) would look at the 3rd column of the table below, which shows row 17. There is a secondary set of formulas that use the row # listed in the final column, which would be row #19 ijn this case. Now obviously, if the date is 2/1/2016 in cell C20, that means cell D20 would be 2016, and the table below shows that the range would be data_2016, and the row #s used would be 27, and then 29. I require all these to change based on these dates. Does that make sense?

Year	Range	1st Row	2nd Row
2014	data_2014	7	9
2015	data_2015	17	19
2016	data_2016	27	29

It would help if you had some sample data (a file) to work on, but I *think* you can do this with INDIRECT(). Something like...
=vlookup(criteria,indirect("data_"&year(C20)),column-number,0)

Cantosh,
I will create a sample workbook for you right now...

Okay guys, attached is the sample file... sorry it took so long... but here it is

1. Sheet 2 - This is where the formulas need to be. In the yellow highlighted area. C20 date is now F1 - K1 and D20 year is now F2 - K2.
2. data - This is the data tab, of course, and each of these yearly tables has a named range. I use the last column (Row #) so I do not have to manually enter (and constantly change the row #s for each table while using a VLOOKUP. I have highlighted the data that I would need to pull for each year based on the date.
3. Codes - An Extras tab, H- K lists my table with the years, named ranges and rows.

I hope this explains everything

1. Your range names start with ist, not Historical_table
2. change your ranges to start in column B

1. Your range names start with ist, not Historical_table
2. change your ranges to start in column B

=VLOOKUP(A8,INDIRECT("ist_"&YEAR(F1)),2,FALSE)
returns 65.6%

sorry for the double-posts (forum-induced)

Okay, I am doing something wrong... I endered that formula, =VLOOKUP(A8,INDIRECT("IST_"&YEAR(F1)),2,FALSE) into F8, and my result is #N/A. I don't understand

As FDibbins points out, your current arrangement is not conducive to VLookup. Shifting your ranges and following his suggestion is likely your wisest move. If you can't do that, or if you want to try a different approach, then read on...

Playing a bit with what you have, I found that the following formula, entered in Sheet2!F8 with Ctrl + Shift + Enter instead of enter, will give you what I believe to be the desired results. You can then fill right through K8 to get ratio 1 for the other months.

=OFFSET(data!$A$1,MATCH(YEAR(F$1),data!$A:$A,0)+4,MATCH(MONTH(F$1),MONTH(data!$C$1:$N$1),0)+1)

Similarly, the formula below entered in F9 with Ctrl + Shift + Enter and filled right will get you ratio 2:

=OFFSET(data!$A$1,MATCH(YEAR(F$1),data!$A:$A,0)+6,MATCH(MONTH(F$1),MONTH(data!$C$1:$N$1),0)+1)

Bonus: Neither of these formulas requires anything from your 'Codes' sheet at all.
Un-bonus: Your date in Sheet2!G1 is wonky. Excel recognizes it as a 2016 date. EOMONTH is not ideal here.

EDIT: EOMONTH is fine. The TEXT arrangement is your problem.

okay, I see it... just changed the range... I didn't notice that line...

So did that work for you?

Remember that the VLOOKUP search criteria must be in the very 1st column of the range

Okay wait, I have another question... I went back and looked at the data since it did not work right on my real file... 65.6% is Ratio 1 for January 2015 (Column C). Cell F1 lists a date of November 2015... which is 46.3% (Column M). I was under the impression that once the correct table was selected the date would be used to pull the proper colum, and Column O could be used to acknolwedge the correct row # to be used.

And this data will be added to on a regular basis by someone who I have no clue what their "expertise level" is within excel, so I have to take as much guesswork out as possible. which is why I opted for this type of set-up

OK here is a suggestion that will eliminate all of the problems here.

Instead of having a separate table for each year, have all data in 1 table, something like this...

	A	B	C	D	E	F	G	H	I
1			Account		Value A	Value B	Ratio 1	Value C	Ratio 2
2	Jan-14	Jan 2014	Actual		1750978.9	1344651.5	79.6%	0.0	5.2%
3	Feb-14	Feb 2014	Actual		1431365.9	1214846.2	87.1%	0.0	10.9%
4	Mar-14	Mar 2014	Actual		1869871.6	1251333.7	70.0%	0.0	19.5%
5	Apr-14	Apr 2014	Actual		5052216.5	3810831.5	68.7%	0.0	6.9%
6	May-14	May 2014	Actual		1670425.4	1110914.2	46.8%	0.0	5.7%
7	Jun-14	Jun 2014	Actual		2413451.9	1077997.7	48.9%	0.0	-1.2%
8	Jul-14	Jul 2014	Actual		2327905.3	1096128.2	62.9%	0.0	11.0%
9	Aug-14	Aug 2014	Actual		6411782.5	3285040.1	52.0%	0.0	11.3%
10	Sep-14	Sep 2014	Actual		2071208.9	1227914.5	50.7%	0.0	4.7%
11	Oct-14	Oct 2014	Actual		1929656.2	956154.4	45.2%	0.0	9.5%
12	Nov-14	Nov 2014	Actual		2399164.9	1181825.5	37.5%	0.0	14.0%
13	Dec-14	Dec 2014	Actual		6400030.1	3365894.4	43.4%	0.0	11.5%
14	Jan-15	Jan 2015	Actual		1823271.0	1207571.0	65.6%	0.0	0.2%
15	Feb-15	Feb 2015	Actual		2055261.0	1179609.5	57.1%	0.2	11.5%
16	Mar-15	Mar 2015	Actual		1819033.5	1264725.8	69.5%	0.1	5.0%
17	Apr-15	Apr 2015	Actual		1980949.1	1241643.7	63.3%	0.1	4.6%
18	May-15	May 2015	Actual		2195907.2	1270695.3	59.4%	0.1	3.6%
19	Jun-15	Jun 2015	Actual		2774393.4	1232944.2	45.0%	0.0	1.8%
20	Jul-15	Jul 2015	Actual		2623198.5	1297818.7	49.6%	0.1	4.2%

Then to get your answers...

C

D

E

F

G

H

I

J

K

L

M

N

1

Frequency

Trend

Nov-15

Dec-15

Jan-16

Feb-16

Mar-16

Apr-16

Target

WTD

MTD

2

2015

2016

2016

2016

2016

2016

3

Ratio 1

46.3%

39.3%

79.0%

54.8%

65.8%

57.8%

4

Ratio 2

F3=VLOOKUP(EOMONTH(F$1,-1)+1,Sheet1!$A$2:$I$37,MATCH($C3,Sheet1!$A$1:$I$1,0),0)
copied across and down