Match & Index??

B

Betty Csehi

I have the following info in different workbooks. In workbook 1, I have in
Columns A,B,C,D:

Mark & No. Start End Deal
SLGG1234 3/15/2004 6/15/2004 211
SLGG1234 1/1/2004 3/14/2004 111
SLGG1234 6/16/2004 8/15/2004 311
SLGG1255 2/13/2004 8/15/2004 411

In workbook 2, I have Columns A,B, C:

Mark & No. Date Deal
SLGG1234 3/14/2004
SLGG1234 6/14/2004

In Column C of workbook 2, I want a formula to look at Cols A and B.
Compare the info in them to Cols A,B and C in workbook1. If Col As match,
then look at the date in workbook 2, if the date in workbook 2 falls between
the dates in workbook 1, then give me that deal # .

Can this be done with a simple formula or is there some VB that needs to be
written? (I'm not good at VB!)

Thanks!
 
F

Frank Kabel

Hi
try
=SUMPRODUCT(--('sheet1!'$A$1:$A$100=A1),--('sheet1'!$B$1:$B$100>=B1),--
('sheet1'!$C$1:$C$100<=B1),'sheet1'!$D$1:$D$100)
 
B

Betty Csehi

Doesn't seem to work. I get zero?

Frank Kabel said:
Hi
try
=SUMPRODUCT(--('sheet1!'$A$1:$A$100=A1),--('sheet1'!$B$1:$B$100>=B1),--
('sheet1'!$C$1:$C$100<=B1),'sheet1'!$D$1:$D$100)
Click to expand...
 
F

Frank Kabel

Hi
are your deal values numbers? e.g. is 211 stores a a number. also check
if the dates are really dates
 
F

Frank Kabel

Hi
convert the number stored as text (as a result of the concatenate
function) to a real number
 
E

Erin

The < and > signs are backward. I was able to get it to
work once I fixed that. Before I fixed it I too was
getting zero because the dates in column B of sheet2 were
out of the range of the dates in B and C of sheet1 when
 
D

Dave Peterson

=sumproduct() likes to deal with numbers.

'sheet1!'$a$1:$a$100=A1
will return a series of true/falses.

The first minus (not really a dash) converts True/falses to numbers
(-1/0, negative 1 and negative zero= zero)

The second minus changes those -1's to +1's.

Try it on a test formula, but limit your range to just 5 cells (a1:a5). Then
highlight the sheet1!a1:a5=a1 and hit F9. You'll see something like:
{TRUE;FALSE;FALSE;FALSE;FALSE}
include the closest minus:
{-1;0;0;0;0}
and include both minuses.
{1;0;0;0;0}

And when =sumproduct() multiplies each term, it'll return a 1 (1*1 or 0
(anything times 0).

Include the values in D1:d100 and you'll either get 1*something or 0*something.
And then =sumproduct() adds them up.
 
B

Betty Csehi

You guys are great! Thanks so much.
Betty
Erin said:
The < and > signs are backward. I was able to get it to
work once I fixed that. Before I fixed it I too was
getting zero because the dates in column B of sheet2 were
out of the range of the dates in B and C of sheet1 when
Click to expand...
 
Top