Lookup Key Value and Return Month-End of Date Range

[bamatide]

Hi,

I have never run into an issue that I couldn't solve myself, or search for a solution to....but I'm stumped.

I have two worksheets.

The first has a business unit key value for numerous business units, transaction dates, and transaction amounts (Col A, B, C respectively).

The second has the same business unit key value, each business units actual month-end closing date (usually a few days after the month-end, but varies), and the actual month that each closing date corresponds to (Col A, B, C respectively).

I need to find a way to look up the business unit from each sheet, and return the month-end period that each transaction date corresponds to, by determining what date range the transaction date falls into for the actual month-end close period.....which can be different for each business unit and each month.

For example, business unit 1 has a transaction date of 1/3/11, but they did not close the December books for month-end until 1/6/11, so the actual month-end period is December.

I have 6 month-end periods in total from July-December 2010, for over 100 business units, and about 180k transactions. I am having a hard time finding the right formula compound to accurately pull the actual month-end for each business units transaction date, by determining where it falls in the July-December month-end close date range for each specific business unit (which again varies for each business unit and month).

Any help is greatly appreciated!

 

[Blade Hunter]

Do you mean something like this?

=DATE(YEAR(B1),MONTH(B1),1)-1

Where the date is in B1

You can then format it like so:

=TEXT(DATE(YEAR(B1),MONTH(B1),1)-1,"MMMM YYYY")

 

[barry houdini]

In the first sheet D2 try this "array formula"

=INDEX(Sheet2!C$2:C$1000,SMALL(IF(A2=Sheet2!A$2:A$1000,IF(B2<=Sheet2!B$2:B$1000,ROW(Sheet2!B$2:B$1000)-ROW(Sheet2!B$2)+1)),1))

confirmed with CTRL+SHIFT+ENTER and copied down

 

[bamatide]

In the first sheet D2 try this "array formula"

=INDEX(Sheet2!C$2:C$1000,SMALL(IF(A2=Sheet2!A$2:A$1000,IF(B2<=Sheet2!B$2:B$1000,ROW(Sheet2!B$2:B$1000)-ROW(Sheet2!B$2)+1)),1))

confirmed with CTRL+SHIFT+ENTER and copied down

Thanks Barry. This seems to be only returning the same month-end date for all transactions though (the earliest date).

I'm trying to understand what the SMALL function is trying to accomplish.

See below:

Sheet 1
BU-- Trans Date-- Amount
1-- 12/3/10-- 100
1-- 1/4/11-- 50
2-- 12/3/10-- 100
2-- 1/4/11-- 200

Sheet 2
BU-- Month Close Date-- Month End
1-- 12/4/10-- November 2010
1-- 1/6/11-- December 2010
2-- 12/1/10-- November 2010
2-- 1/3/11-- December 2010


In the above example, BU #1 transactions would fall in November and December 2010, as they happened before the cut off close date. However, BU #2 transactions would fall in December 2010 and January 2011 as they fell after the close date.

As I have hundreds of thousands of these transactions and dates, and different closing dates for a lot of BUs and months, I need a function that will be able to lookup each BU's specific close date, for each month, and return the actual Month End in cell D of sheet 1.

Any additional thoughts?

 

[barry houdini]

OK, yes, you're right, I used SMALL, that should be LARGE, apologies

Make sure you confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula and it should work OK.

The IF functions should find all the rows where the BU matches and the end date is later, then you take the largest of those (I'm assuming the end dates are listed in order) to get the one immediately after that date.....INDEX then retrieves the relevant month from column C.....does that sound right?

Rather than look through all 1000 rows (or whatever) it might be possible to make the formula more efficient by just isolating those rows for a specific BU, I assume that the end dates for one BU are all listed together in one block, are there always 6 rows for each BU?

 

[bamatide]

OK, yes, you're right, I used SMALL, that should be LARGE, apologies

Make sure you confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula and it should work OK.

The IF functions should find all the rows where the BU matches and the end date is later, then you take the largest of those (I'm assuming the end dates are listed in order) to get the one immediately after that date.....INDEX then retrieves the relevant month from column C.....does that sound right?

Rather than look through all 1000 rows (or whatever) it might be possible to make the formula more efficient by just isolating those rows for a specific BU, I assume that the end dates for one BU are all listed together in one block, are there always 6 rows for each BU?

That did it! I had to play with it for a while though, because I was using named ranges for sheet 2 and confirming it using them did not like it for some reason. But it is working now! Thanks so much Barry!

To answer your questions about making it more efficient, yes the end dates are all together in blocks by BU. There are actually 8 rows in each block though, as I wasn't sure if I needed the month before and after my July-December date range to properly calculate the end dates.

Thanks again!