Month End Date evaluation via lookup

[Erik_Pgh]

I have a table that has our month end close date (typically 1 week before the real month end). I need to evaluate a dates in another table to provide a month end date in the format of YYYY-MM.

Ex

Lookup table:
Month End
1/21/2011 2011-01
2/18/2011 2011-02
3/25/2011 2011-03
etc.


the date being evaluated 1/25/2011 = 2011-02

in essence it has to look for what gap the date falls within.

 

[Richard Schollar]

Hi

A simple VLOOKUP should suffice:

=VLOOKUP(YourDate,LookupTable!A:B,2,TRUE)

Replace YourDate with the cell ref containing your date (eg A1) and LookupTable!A:B with whatver range contains your lookup table. The impoirtant thing is for the 4th argument to be TRUE.

 

[jim may]

Not sure why this isn't working...

Excel Workbook
	A	B	C	D	E
1	1/21/2011	2011-01		1/25/2011	2011-01
2	2/18/2011	2011-02
3	3/25/2011	2011-03
Sheet1

Excel 2007

Cell Formulas
Range	Formula
E1	=VLOOKUP(D1,A:B,2,TRUE)

 

[Marcol]

Maybe this?

Excel Workbook
	A	B
1	Date	Falls In Period
2	21/01/2011	2011-01
3	18/02/2011	2011-02
4	25/03/2011	2011-04
Sheet1

 

[barry houdini]

Richard's method works if the dates are start dates for each month - can you set it up that way?

 

[Richard Schollar]

D'Oh! A Vlookup won't suffice as it will return the earlier month end date. As long as the lookup table starts with an obviously false value (like 0) then you could use this derivative:

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Date</td><td style=";">M/e</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">0</td><td style=";">N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">21/01/2010</td><td style=";">2010-01</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">21/02/2010</td><td style=";">2010-02</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">24/03/2010</td><td style=";">2010-03</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Date</td><td style=";">M/e date</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">25/01/2010</td><td style=";">2010-02</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">21/02/2010</td><td style=";">2010-02</td><td style=";">===assuming you want this to fall into 2010-02</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">01/03/2010</td><td style=";">2010-03</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B14</th><td style="text-align:left">=INDEX(<font color="Blue">$B$5:$B$8,MATCH(<font color="Red">A14-0.0001,$A$5:$A$8,TRUE</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />

The deduction of 0.0001 just ensures that a date matching a m/e date falls into that m/e date and not the next one.

 

[Marcol]

Is there something wrong with the formula in Post #4, other than it looks long? ...

 

[Erik_Pgh]

Richard's method works if the dates are start dates for each month - can you set it up that way?

Yes there is an offset by 1 month but additionally there is a problem if something falls on the date exactly it then falls into the next month.

 

[Erik_Pgh]

D'Oh! A Vlookup won't suffice as it will return the earlier month end date. As long as the lookup table starts with an obviously false value (like 0) then you could use this derivative:

Excel 2002<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Date</td><td style=";">M/e</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">0</td><td style=";">N/A</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">21/01/2010</td><td style=";">2010-01</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">21/02/2010</td><td style=";">2010-02</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">24/03/2010</td><td style=";">2010-03</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">Date</td><td style=";">M/e date</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">25/01/2010</td><td style=";">2010-02</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">21/02/2010</td><td style=";">2010-02</td><td style=";">===assuming you want this to fall into 2010-02</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">01/03/2010</td><td style=";">2010-03</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">B14</th><td style="text-align:left">=INDEX($B$5:$B$8,MATCH(A14-0.0001,$A$5:$A$8,TRUE)+1)</td></tr></tbody></table></td></tr></tbody></table>


The deduction of 0.0001 just ensures that a date matching a m/e date falls into that m/e date and not the next one.

The match seems to be working. The key is the "-1" match type

-1 MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

Thanks.

Erik from Da Burgh (go Stillers)

 

[Erik_Pgh]

my final solution
http://www.mrexcel.com/forum/showthread.php?t=624526