VLookup a value within a date range in a different date range

[glenda75]

Hi!


I have a worksheet in which I have to bring an amount from another table but there are multiple criterias that have to be met.


ie:


this is what I am looking for

[pre]

Contract id                 dates                     price?

12345          3/1/2009        3/31/2009
this is the data that I am looking for into:

Contract id                 dates                           price

12345                     12/1/2009 -1/2/2010        10.00

12345                    1/1/2009 - 5/31/2009          6.00

[/pre]
So the result that I should yield is the $6.00 because it is the same contract id and the march date falls within that range.


HELP!!! I can upload the file if needed...


thank you!!

 

[Luke M]

Something like:

=SUMPRODUCT((IDRange=ID)*(StartDateRange<=StartDate)*(EndDateRange>=EndDate)*(PriceRange))

Replace each of those words with correct cell/range references.

 

[glenda75]

Hello!


But my start date is also between a range. The IDRange listed in your formula just refers to one cell?

 

[SirJB7]

Hi, glenda75!


Luke M's formula is "filtering" by these conditions:

- IDRange = ID

- StartDateRange <= StartDate

- EndDateRange >= EndDate


So regarding your first comment, dates in StartDateRange are considered only if less or equal to Start date (same applies to end dates if great or equal), and regarding your last question, all ids in IDRange equal to ID are considered too.


Hope it helps.


Regards!

 

[glenda75]

Hello!


I'm sorry but we tried the formula and it didn't seem to work.


Can you please take a look at the link and see what is wrong with the formula?


https://skydrive.live.com/?cid=0F7F1A994B7EEA62&id=F7F1A994B7EEA62%21110


thank you sooo much....


Glenda

 

[Faseeh]

@glenda75,


The file is asking for username and password, please make it public.


Regards,

 

[glenda75]

Hi! Okay I made it public... please try now...


thank you!

 

[SirJB7]

Hi, glenda75!

In E3 use this formula, following Luke M indications:

=SUMAPRODUCTO(($A$8:$A$9=$A$3)*($B$8:$B$9<=$C$3)*($C$8:$C$9>=$C$3)*($D$8:$D$9)) -----> in english: =SUMPRODUCT(($A$8:$A$9=$A$3)*($B$8:$B$9<=$C$3)*($C$8:$C$9>=$C$3)*($D$8:$D$9))

Regards!