Curly calc -- Lookup? Match?

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi, struck a problem that has me losing what hair remains.
Basically, the layout is this:
CashFlow by Half Yr.xls
ABCDEFGH
1
2
3Jun-04Dec-04Jun-05Dec-05Jun-06Dec-06Jun-07Dec-07
410000050000
5
6
7
8PmtMonthPmtDate
9Jul-0550000
10Dec-0550000
11Mar-0650000
12
Sheet1

B8:C11 contain a lookup table of dates and values.
Row 3 contains dates by half-year.
I want to place payments from the lookup table in Row 4, summing where required.
Does anyone have any ideas?

Thanks
Denis
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Thanks tactps,
the problem is that not all the dates will match -- they can be any month in the lookup table and only Jun or Dec in the flow. I was looking at comparing $B$9:$B$11 to something like EDATE(C3,-5) to C3, to cover the half-year, and that's the bit that fried the brains.

Denis
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,023
put this in cell a4
=SUMPRODUCT(($B$9:$B$11<=A3)*$C$9:$C$11)
in cell b4
=SUMPRODUCT(($B$9:$B$11>A3)*($B$9:$B$11<=B3)*$C$9:$C$11)

Then copy cell b4 to succeeding column
 

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,936
Hi Denis,

It can be done simply by Pivot Table.

Just add column B and format to Date

Eli
Book1
ABCDEFG
1
2monthperiodpmtSumofpmt
3Jul-0512-20055000periodTotal
4Dec-0512-2005500012-200510000
5Mar-0606-2006500006-20065000
6GrandTotal15000
7
Sheet1
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Thanks guys,

EliW -- Pivot Table would normally be great but this is part of something where I couldn't change the layout. I hadn't thought of using CEILING and DATEVALUE like that before -- that will come in very handy for another problem I'm working on

ChitoSunday -- spot on!

Thanks again
Denis
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,025
Messages
5,856,912
Members
431,837
Latest member
megantang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top