Change Sheet name in Vlookup when formula copies to right

[howard]

I have the following Vlookup formula

=VLOOKUP($A2,'Sept-2018'!C:D,2,FALSE)

I would like to know how to automatically change to the sheet name in the formula when copying the formula to the right for eg 'Oct-2018'!, 'Nov-2018'! etc


Your assistance is most appreciated

 

[sheetspread]

=VLOOKUP($A2,INDIRECT("'"&TEXT(1+EOMONTH(DATEVALUE("9/1/2018"),COLUMN(A1)-2),"MMM-YYYY")&"'!C:D"),2,FALSE)

works if you change Sept to Sep

 

[howard]

Thanks for the help


Please explain this part of the formula to me

 INDIRECT("'"&TEXT(1+EOMONTH(DATEVALUE("9/1/2018"),COLUMN(A1)-2),"MMM-YYYY")&"'!

 

[sheetspread]

To get the date:

Excel 2010
	A	B	C	D	E	F	G
3	Sep-2018	Oct-2018	Nov-2018	Dec-2018	Jan-2019	Feb-2019	Mar-2019
Sheet3

Cell Formulas
Range	Formula
A3	=TEXT(1+EOMONTH(DATEVALUE("9/1/2018"),COLUMN(A1)-2),"MMM-YYYY")

EOMONTH returns the last date of the month:

Excel 2010
	A	B	C	D	E	F	G
3	8/31/2018	9/30/2018	10/31/2018	11/30/2018	12/31/2018	1/31/2019	2/28/2019
Sheet3

Cell Formulas
Range	Formula
A3	=EOMONTH(DATEVALUE("9/1/2018"),COLUMN(A1)-2)

Column(A1) will start at 1 and increase by 1 each time you drag right (notice how 2 is subtracted so it starts at -1, i.e. t-1).

The text function converts a date to a string

Indirect converts text to a cell reference that the vlookup can read as such.

 

[howard]

Thanks for the explanation