Match Formula for three fields to produce result

[AHK]

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!

 

[T. Valko]

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.

 

[Nadine]

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]

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.

 

[Nadine]

I now understand the "using cells to hold the lookup criteria".
I entered
=SUMPRODUCT(--('AD-InvoiceDetails'!A:A='PO-SummaryInfo'!M5),--('AD-InvoiceDetails'!B:B='PO-SummaryInfo'!N5),--('AD-InvoiceDetails'!C:C='PO-SummaryInfo'!O5),'AD-InvoiceDetails'!Q:Q) and received #NUM!
Col A is the alpha numeric invoice #
Col B is the PO#
COl C is the 2 character identifier
Col Q is the invoice total
On the Summary worksheet where the formula is entered into cell R5, Col
M=Invoice #, Col N=PO, COl O=identifier.

Any idea what I did wrong? Thanks so much!!!!!

 

[T. Valko]

What version of Excel are you using?

Unless you're using Excel 2007 or later you *can't* use entire columns as
range references with SUMPRODUCT. Use a smaller specifc range.