VLOOKUP MONTH & YEAR formula

[ortmll]

I need to update my formula so that it captures the month & year of a date.
Here is what I have now: =IFERROR(VLOOKUP(MONTH(D8),Months,2),"")

It works perfectly when all of the months are in the same year, but since the new year it is unable to differentiate between 2014 & 2015.
My gut tells me this is an easy fix, but my brain has thus far been unable to deliver.

• D8 represents the cell containing my date.
• "Months" represents my table containing the months of the year
• I added the column containing the years to expand this formula, but so far it has not worked

Any help would be appreciated[TABLE="width: 334"]
<TBODY>[TR]
[TD]


[TABLE="width: 334"]
<TBODY>[TR]
[TD]
Months</SPAN>
[/TD]
[TD]Column1</SPAN>
[/TD]
[TD]Column2</SPAN>
[/TD]
[/TR]
[TR]
[TD]""</SPAN>
[/TD]
[TD]Null</SPAN>
[/TD]
[TD]Null</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]Jan</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]Feb</SPAN>
[/TD]
[TD]2015</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]Mar</SPAN>
[/TD]
[TD]2016</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]Apr</SPAN>
[/TD]
[TD]2017</SPAN>
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]May</SPAN>
[/TD]
[TD]2018</SPAN>
[/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]Jun</SPAN>
[/TD]
[TD]2019</SPAN>
[/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]Jul</SPAN>
[/TD]
[TD]2020</SPAN>
[/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD]Aug</SPAN>
[/TD]
[TD]2021</SPAN>
[/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD]Sep</SPAN>
[/TD]
[TD]2022</SPAN>
[/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD]Oct</SPAN>
[/TD]
[TD]2023</SPAN>
[/TD]
[/TR]
[TR]
[TD]11</SPAN>
[/TD]
[TD]Nov</SPAN>
[/TD]
[TD]2024</SPAN>
[/TD]
[/TR]
[TR]
[TD]12</SPAN>
[/TD]
[TD]Dec</SPAN>
[/TD]
[TD]2025</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[btomjack]

If I were doing it, I would change the column you created to add the year to be a combination of month & year (Jan-2015 or 1-2015 or something similar). Then you can modify the VLOOKUP to be =IFERROR(VLOOKUP(MONTH(D8)&"-"&YEAR(D8),Months,2),"").

I would recommend just testing the formula =MONTH(D8)&"-"&YEAR(D8) in a random cell so that you can see what the VLOOKUP formula will be looking for, then change your "year" column to match that.

 

[ortmll]

First of all, thank you for your help!</SPAN>

I made the adjustments you suggested to my “Months” table and the formula: =MONTH(D8)&"-"&YEAR(D8) worked perfectly in my test cell.</SPAN>

Unfortunately, when I applied the formula: =IFERROR(VLOOKUP(MONTH(D8)&"-"&YEAR(D8),Months,2),"") it returned a value of “NULL” from my table.</SPAN>

Any thoughts?</SPAN>

 

[btomjack]

Is the column you added (initially as just year, then modified to be month & year) on the left side of the table? In order for VLOOKUP to work, the value you're searching for has to be in the far left of the search range.

 

[rollis13]

Cross-post: VLOOKUP MONTH & YEAR formula

 

[ortmll]

Is the column you added (initially as just year, then modified to be month & year) on the left side of the table? In order for VLOOKUP to work, the value you're searching for has to be in the far left of the search range.

Here is what the table looks like. the month/year is set up in the right column. However, when I moved it to the far left my formula returned a blank. No longer the "NULL" result, but at least the null was in the same column as my month/year combos.
=IFERROR(VLOOKUP(MONTH(C4)&"/"&YEAR(C4),Months,2),"")
[TABLE="width: 199"]
<TBODY>[TR]
[TD]Months[/TD]
[TD]Column1[/TD]
[/TR]
[TR]
[TD]""[/TD]
[TD]Null[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jan/2014[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Feb/2014[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mar/2014[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apr/2014[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]May/2014[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jun/2014[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jul/2014[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Aug/2014[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Sep/2014[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Oct/2014[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Nov/2014[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Dec/2014[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Jan/2015[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Feb/2015[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Mar/2015[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Apr/2015[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]May/2015[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Jun/2015[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Jul/2015[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Aug/2015[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Sep/2015[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Oct/2015[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Nov/2015[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Dec/2015[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]