array or IF formula problem (?)

[Mark W]

I'm having problems with the following formula:

=IF(A2=$C$2:$C$438,B2-$D$2:$D$438)

It works fine until the values in column C are not in the
identical row as column A. The way I read it, it should
find if the value of the specified row in column A is
anywhere between C2 and C438 then the value in the
corresponding row of D2438 should be subtracted from B.

What am I doing wrong? A sample of the spreadsheet and the
problem I'm having is below.

c00042230.xml 10/30/2003 14:20 c00042230.xml
10/3/2003 10:06 27.17599537
c00042231.xml 10/30/2003 15:16 c00042231.xml
10/3/2003 10:06 27.21496528
c00042232.xml 10/30/2003 14:26 c00042232.xml
10/3/2003 10:07 27.17987269
c00042587.xml 10/29/2003 13:14 c00042291.pdf
10/3/2003 15:18 FALSE
c00042588.xml 10/29/2003 13:15 c00042301.pdf
10/3/2003 15:54 FALSE
c00042589.xml 10/29/2003 13:31 c00042587.xml
10/7/2003 12:04 FALSE
c00042590.xml 10/29/2003 13:31 c00042588.xml
10/7/2003 12:04 FALSE

 

[Leo Heuser]

Mark

One way:

=SUMPRODUCT((A2=$C$2:$C$438)*(B2-$D$2:$D$438))

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

 

[Mark W]

Leo,

Thank you so much--this works perfectly. You've saved me a
ton of time!

Mark

 

[Leo Heuser]

You're welcome, Mark.
I appreciate your feedback!

LeoH