Lookup formula with multiple criteria - find if date is within range

[mazarin]

Hi.
I need some help from you experts with a lookup formula (please check the attached image and/or xls).

Let me explain what I need in steps:

1. I have payment contracts with my customers.

2. The contract list has the following information:

Column A = Customer name
Column B = Contract Start Date
Column C = Contract End Date
Column D = Contract Payment Value

Each company can have only one contract per month, and when each contract expires, a new one, with a new Payment value begins.

3. Each contract have a duration of 3 months, like, 01/01/2015 to 31/03/1015 (date format dd/mm/yyyy).

4. Finally, what I need is to make a yearly table that shows the payment value for each month (January = 50.00, February = 50.00, March = 50.00, April = 100.00... )

So, Let's assume that I want to show the February/2015 payment for Customer A, from my Contracts list. How to I do it?

After hours of "Googling" and trial and error, I still couldn't find out how to do it.

If I was using exact dates, a simple INDEX MATCH function would be enough. But it needs to look for CUSTOMER A's February/2015 (in fact 01/02/2015) payment, in a list containing list containing date ranges, and not exact dates.

Thanks in advance!

[kvsrinivasamurthy]

Formula in G3, then drag across

Please Login or Register  to view this content.

[samba_ravi]

G3=sumproduct(($a$3:$a$7=$f3)*($b$3:$b$7<=g$2)*($c$3:$c$7>=g$2)*($d$3:$d$7))
try this and copy across

[mazarin]

Formula in G3, then drag across

Please Login or Register  to view this content.

Hi, kvsrinivasamurthy. Thanks for your help! But this formula is only showing the "Payment" for the exact dates of "Monthy Payment table". I need to show the payment value for each month.

[mazarin]

G3=sumproduct(($a$3:$a$7=$f3)*($b$3:$b$7<=g$2)*($c$3:$c$7>=g$2)*($d$3:$d$7))
try this and copy across

Hi, NFLSALES. It solved everything! Thank you very much! As kvsrinivasamurthy solution, this is a different formula than I was expecting, so I will take a closer look and try to learn how does it work.

[bebo021999]

This if for start date within month of Monthly payment (i.e start 31/1/2015 or 1/1/2015, both are counted whole month Jan/2015 payment)

Please Login or Register  to view this content.

[mazarin]

This if for start date within month of Monthly payment (i.e start 31/1/2015 or 1/1/2015, both are counted whole month Jan/2015 payment)

Please Login or Register  to view this content.

Thanks, Bebo! Your formula is a little different from the above and also fits my needs.

[mazarin]

So, a INDEX + MATCH + some other functions would never solve this?

[Czeslaw]

An array formula (Ctrl+Shift+Enter).

Please Login or Register  to view this content.

[mazarin]

An array formula (Ctrl+Shift+Enter).

Please Login or Register  to view this content.

That's more os less where I could get, but as I said before, it is only showing the "Payment" for the exact dates of "Monthy Payment table". I need to show the payment value for each month.

Until now, SUMPRODUCT seems to be the only way, but I may be wrong.

[mazarin]

G3=sumproduct(($a$3:$a$7=$f3)*($b$3:$b$7<=g$2)*($c$3:$c$7>=g$2)*($d$3:$d$7))
try this and copy across

Hi, NFLSALES. I've studied SUMPRODUCT function itself, and based on your solution, now I understand how does it work. But what if I had text instead of a numerical value? In fact, in my real spreadsheet have other columns with text information which I also need to get.

[azumi]

Maybe somthing like this?

[mazarin]

Maybe somthing like this?

Hi Azumi. It is an interesting solution. But it works only with numbers and not with Text.

[azumi]

I don't know what you mean about text, but another try with 2 formulas

[kvsrinivasamurthy]

See the changed formula.file attached.

Please Login or Register  to view this content.

[samba_ravi]

Attach a sample file where you are not getting result

[Czeslaw]

Another nonmassive formula.

Please Login or Register  to view this content.

[mazarin]

I don't know what you mean about text, but another try with 2 formulas

Hello, AZUMI. What I meant by TEXT, is to have, letters or any other character in the "Payment" Column: Instead of numbers like 100.00, 150.00, 200.00, I have TEXT and specials characters, like: AAA, BBB, C$C...
And your formula does exactly that! Thank you very much

[mazarin]

Attach a sample file where you are not getting result

For sure. Here it is:

[Vikas_Gautam]

Try this in G3, Drag down and sideways to right:-

Please Login or Register  to view this content.

Check the attached file:-

[mazarin]

Another nonmassive formula.

Please Login or Register  to view this content.

Great solution, CZESLAW. It works exactly as I expected. thank you!

[mazarin]

Try this in G3, Drag down and sideways to right:-

Please Login or Register  to view this content.

Check the attached file:-

Hello, Vikas. I like this SUMIFS formula. Solves when we need to lookup for a NUMBER, and other experts here are suggesting good alternatives for when we need to lookup for TEXT.

[Vikas_Gautam]

NO Problems.. Maz.. enjoy the solutions..!