What do you mean "using cells to hold the lookup criteria"?

Instead of hard coding the lookup criteria in the formula like this:

=INDEX(Comments,MATCH(1,IF(ID=123456,IF(Document=" Profile
Statement",IF(DATE=DATE(2009,12,31),1))),0))

Use cells to hold those criteria like this:

A1 = 123456
B1 = Profile Statement
C1 = 12/31/2009

=INDEX(Comments,MATCH(1,IF(ID=A1,IF(Document=B1,IF (DATE=C1,1))),0))

To do a conditional sum based on the same criteria:

=SUMPRODUCT(--(ID=A1),--(Document=B1),--(DATE=C1),Range_to_Sum)

If you're using Excel 2007 or later you might be able to use the SUMIFS
function which is more efficient than SUMPRODUCT.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
T. Valko,
What do you mean "using cells to hold the lookup criteria"? I'm confused
by
that.
Can you give the formula to SUMIF the same thing? Meaning, I'd like to
sum
a column if conditions 1,2,and3 are met or maybe even more conditions.
Thanks
Nadine

"T. Valko" wrote:

Using cells to hold the lookup criteria...

A1 = some ID number
B1 = some document type
C1 = some date

Then, array entered** :

=INDEX(Comments,MATCH(1,IF(ID=A1,IF(Document=B1,IF (DATE=C1,1))),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"AHK" wrote in message
...
ID # Document Date Comments
123456 Profile Statement 12/31/2009 Will be returned this week

Can someone help with a match formula that will match ID#, Document,
and
Date (needs to match all three) and generate the text listed the
comments
column. Thank you in advance!


.