Sumproduct function allow a lookup for one Date to check if it fallsin Date Range in other table

[Dave K]

Hello - I am trying compare the date for a given list of Unique Names
in one table (Table 1) to another table (Table 2) that has the Unique
Names (though there may be multiple instances in Table 2) along with a
From Date and Thru Date for each record (i.e., for each name).

I am hoping to come up with a formula that will allow me to confirm
when the date in Sheet1 finds a hit in sheet 2, meaning that the date
for a given name in Sheet 1 is within the From and Thru Dates in Sheet
2 (when there is a match on Name).

The below description will hopefully make it clear.

In Sheet1, I have the following Column Headers
(A) Unique_ID
(B) Date

In Sheet2, I have the following Column Headers:
(A) Unique ID
(B) From Date
(C) Thru Date
(D) NumerOfInterest

In Sheet 1, I am trying to place a lookup or sumproduct formula in
Column C that will give me the value in Column D in Sheet2, when there
is a "hit" (i.e., when the lookup name in sheet 1 matches Sheet2 AND
when the date in sheet 1 is between the effective dates in Sheet2.

The dates that are in play are numeric values in YYYYMM format, if it
helps.

Thanks for any suggestions or assistance!

 

[isabelle]

hi Dave,

=SUMPRODUCT(--(Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10<B2)*(Sheet2!$C$2:$C$10>B2)*Sheet2!D210)


--
isabelle



Le 2012-04-06 16:59, Dave K a écrit :

 

[Dave K]

Thanks so much for the response Isabelle - Do you happen to know why i
may be getting a "#Value!" results when i apply that formula?

I am using Excel 2010, if that makes a difference.

 

[Dave K]

Thanks so much for the reply.

Any thoughts as to why I am still getting a "#Value!" error when i
apply that formula?

 

[isabelle]

i have made an example, i hope that will be helpful

http://cjoint.com/?BDhaoB6awtr

--
isabelle



Le 2012-04-06 17:49, Dave K a écrit :

 

[Dave K]

FYI, i did get it to work partly, but only if i apply all of the
formula, except for the last part (i.e., "*Sheet2!D210")

So i get the resulting value "1" when a match is found (if i eliminate
that final clause in your formula). But i haven't been able to
generate the value in Column D in Sheet 2, since I had to drop that
last part of the formula.

 

[isabelle]

just change the last part "Sheet2!D210" for "Sheet2!$D$2:$D$10"

--
isabelle



Le 2012-04-06 18:26, Dave K a écrit :

 

[Dave K]

that is great. thanks again isabelle!

One follow up. Is there a way to simply display the Value in Sheet2
Column D when there is a "hit", rather than show the summation of the
values in Column D?

In other words, your example shows the number 4 in Sheet1!C2. I
actually am trying to simply show an alphanumeric value for the first
hit, rather than sum up all of the values where there is a match.

So if the value "A12345" is in sheet2!D2, I want to be able to display
that value as the result of my formula in Sheet1!C2.

I am getting the #Value error instead.

Does that make sense? Hope i am not confusing you.

 

[isabelle]

can you put an example of your data on this site "jumbofiles or cjoint or another", and give us the generated link

--
isabelle



Le 2012-04-08 14:00, Dave K a écrit :

 

[Dave K]

Sorry I took so long to reply.

The file is located at:

http://jumbofiles.com/h0vpy5dvvoz9