Formula to populate data based on date

Greetings Everyone!

Request for some assistance with a massive task I’ve been pushed against. I’ve enclosed a spreadsheet depicting the layout in which my data is available. I need some assistance to do the following using the Base Date in Column B.

Here’s the explanation:

Populate the data in columns C to H by row

Step 1: Logic for Column C:

Compare the Base Date in Column B with the Date in the 3rd cell (Labeled D) under each of the categories (Colum I onwards)

Add all the values in the 4th column (labeled A) under each category and populate in Column C cell provided the date in the 3rd Column of each category is less than or equal to the base date

Step 2: Populate data for the category whose Date in the 3rd column of each category is the closest to the base date, but does not exceed the base date. In example 1:

Base Date = 2/24/2010

Closest Date = 4/20/2007. As a result, the data in D to G columns have been populated from Category 5

Last step: Column H is the sum from column A of all categories for which the category dates exceed the Base Date

Have enclosed a sample file with the expected output in column C to H. I would be obliged if you could help me with a formula or something that can do this magic for over 2000 rows of data that I have.

Please let me know if anything is unclear or need further explanation.

Best,
Spi

Try array-entering this formula in D3 and filling down to G4.

Formula:

=INDEX($I3:$BP3,,(MATCH(MAX(IF(IF($I$2:$BP$2="D",$I3:$BP3,FALSE)<=$B3,$I3:$BP3)),IF(IF($I$2:$BP$2="D",$I3:$BP3,FALSE)<=$B3,$I3:$BP3),0)-2)+(COLUMNS($A:A)-1))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Originally Posted by FlameRetired

Try array-entering this formula in D3 and filling down to G4.

Formula:

=INDEX($I3:$BP3,,(MATCH(MAX(IF(IF($I$2:$BP$2="D",$I3:$BP3,FALSE)<=$B3,$I3:$BP3)),IF(IF($I$2:$BP$2="D",$I3:$BP3,FALSE)<=$B3,$I3:$BP3),0)-2)+(COLUMNS($A:A)-1))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Thanks so much. This works perfect from my preliminary tests. Could you please also assist me with populating the data in column C and H repectively please...


Thanks again

Originally Posted by spiwere

................... Could you please also assist me with populating the data in column C and H repectively please...

I'm considering several strategies. For one of them I would need to know the largest number you would anticipate in the "A" sections.

Originally Posted by FlameRetired

I'm considering several strategies. For one of them I would need to know the largest number you would anticipate in the "A" sections.

The largest number in section A would be a tricky one this is the revenue amount and could be in the tune of billions. Does this help?

Ouch! Does your present data reflect this? I.e. is 14.2 an expression of 14.2 billion?

Originally Posted by FlameRetired

Try array-entering this formula in D3 and filling down to G4.

Formula:

=INDEX($I3:$BP3,,(MATCH(MAX(IF(IF($I$2:$BP$2="D",$I3:$BP3,FALSE)<=$B3,$I3:$BP3)),IF(IF($I$2:$BP$2="D",$I3:$BP3,FALSE)<=$B3,$I3:$BP3),0)-2)+(COLUMNS($A:A)-1))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

On my real data. I'm getting all #NA is there something I'm doing incorrectley? Please advise.

Please ignore this one. I figured out the reason. It was to do with my header. Sorry.

But could you please help me with updating data in column C and H


Kindest regards,

Spi

Good. Try this in column H. Array-entered.

Formula:

=SUM(IF(IF(ISNUMBER((IF(IF($I$2:$BP$2="D",$I3:$BP3,FALSE)>$B3,$J3:$BQ3))),$J3:$BQ3)<30000,$J3:$BQ3))

I chose an arbitrarily small enough number (30000 equal to 2/18/1982) to filter out dates in this array and large enough not to filter out any amounts.