# Curly calc -- Lookup? Match?

#### SydneyGeek

##### MrExcel MVP
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
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
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
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
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

#### Brian from Maui

##### MrExcel MVP
Chitosunday said:
put this in cell a4
=SUMPRODUCT((\$B\$9:\$B\$11<=A3)*\$C\$9:\$C\$11)

No need for Sumproduct,

=SUMIF(\$B\$9:\$B\$11,"<="&A3,\$C\$9:\$C\$11)

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.

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.

### Which adblocker are you using?

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

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