| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Formula for three fields to produce result
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Formula for three fields to produce result
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Formula for three fields to produce result
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! . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Formula for three fields to produce result
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! . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Formula for three fields to produce result
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" wrote: 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! . . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Formula for three fields to produce result
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. -- Biff Microsoft Excel MVP "Nadine" wrote in message ... 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" wrote: 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! . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| PRODUCE A PRODUCT THAT WILL LIST A STRING OF VALUES FOR A MATCH | Excel Worksheet Functions | |||
| Formula result does not match displayed result | Excel Worksheet Functions | |||
| Why does Concatenate formula show but not produce a cell result? | Excel Worksheet Functions | |||
| can excel produce text in a given cell as a result of a boolean operation? | Excel Discussion (Misc queries) | |||
| produce a formulate to produce assigned seats for dinner | Excel Worksheet Functions | |||
Linear Mode
