| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match from multiple columns
I have the following scenario.
-- Client Worksheet MAILED COMPANY TITLE FIRST LAST Xxxxxxxx Acton Flooring Inc. Ms. Marsha Acton Xxxxxxxx AcuTech Ms. Linda Dendy Xxxxxxxx Adams and Reese Mr. David Hunt Xxxxxxxx Adams and Reese Ms. Deborah Hunt Misc Worksheet COMPANY DATE1 DATE2 TITLE FIRST LAST Acton Flooring Inc. 12/01/05 N Ms. Marsha Acton AcuTech 12/01/05 N Ms. Linda Dendy Adams and Reese N N Mr. David Hunt Adams and Reese 12/01/05 N Ms. Debora Hunt I use the following formula to retrieve my date: =INDEX(Misc!$A:$C,MATCH(C2,Misc!$A:$A,0),2) The problem with this is it will retrieve the €śN€ť for the first occurrence of Adams and Reese. If I mailed a letter to Ms. Debora Hunt, I want the 12/01/05 date to populate the MAILED column. hgopp99 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match from multiple columns
According to your example, C2 on the Client sheet is "Title".
Just changing that to B2, to reference "Company" Try this *array* formula in : =INDEX(Misc!$A$1:$C$20,MATCH(1,(Misc!$A$1:$A$20=B2 )*(Misc!$B$1:$B$20<"N"),0 ),2) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "hgopp99" wrote in message ... I have the following scenario. -- Client Worksheet MAILED COMPANY TITLE FIRST LAST Xxxxxxxx Acton Flooring Inc. Ms. Marsha Acton Xxxxxxxx AcuTech Ms. Linda Dendy Xxxxxxxx Adams and Reese Mr. David Hunt Xxxxxxxx Adams and Reese Ms. Deborah Hunt Misc Worksheet COMPANY DATE1 DATE2 TITLE FIRST LAST Acton Flooring Inc. 12/01/05 N Ms. Marsha Acton AcuTech 12/01/05 N Ms. Linda Dendy Adams and Reese N N Mr. David Hunt Adams and Reese 12/01/05 N Ms. Debora Hunt I use the following formula to retrieve my date: =INDEX(Misc!$A:$C,MATCH(C2,Misc!$A:$A,0),2) The problem with this is it will retrieve the "N" for the first occurrence of Adams and Reese. If I mailed a letter to Ms. Debora Hunt, I want the 12/01/05 date to populate the MAILED column. hgopp99 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match from multiple columns
I did not understand your function. I may need for detain explanation.
Before that,I would like to present another example of the problem; a specific example that we can use to help determine the correct course. Worksheet:Misc Company Date1 Date2 Title First Last American Cast Iron 1/11/2006 N Mr. Stephen Calgore Associated Grocers 1/10/2006 1/01/2006 Mr. Ron Burke Associated Grocers 1/15/2006 1/01/2006 Ms. Alora Reed Associated Grocers 1/20/2006 1/01/2006 Mr. David Vaughn Worksheet:Client Date1 Date2 Company Title First Last - - American Cast Iron Mr. Stephen Calgore - - Associated Grocers Mr. Ron Burke - - Associated Grocers Ms. Alora Reed - - Associated Grocers Mr. David Vaughn I am trying to retreive the date from Misc worksheet and place it in the Client worksheet. I have been using: =INDEX(Misc!$A:$C,MATCH(C7,Misc!$A:$A,0),3) but this works when there is only one instance of a company or all the dates for the company are the same. However, in the example above, there are three different dates for Associate Groces and I would like to know each one. I did not understand the previous reply and hope this will help. Please provide specifics, to make learning a little easier. Thank you. -- hgopp99 "RagDyeR" wrote: According to your example, C2 on the Client sheet is "Title". Just changing that to B2, to reference "Company" Try this *array* formula in : =INDEX(Misc!$A$1:$C$20,MATCH(1,(Misc!$A$1:$A$20=B2 )*(Misc!$B$1:$B$20<"N"),0 ),2) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "hgopp99" wrote in message ... I have the following scenario. -- Client Worksheet MAILED COMPANY TITLE FIRST LAST Xxxxxxxx Acton Flooring Inc. Ms. Marsha Acton Xxxxxxxx AcuTech Ms. Linda Dendy Xxxxxxxx Adams and Reese Mr. David Hunt Xxxxxxxx Adams and Reese Ms. Deborah Hunt Misc Worksheet COMPANY DATE1 DATE2 TITLE FIRST LAST Acton Flooring Inc. 12/01/05 N Ms. Marsha Acton AcuTech 12/01/05 N Ms. Linda Dendy Adams and Reese N N Mr. David Hunt Adams and Reese 12/01/05 N Ms. Debora Hunt I use the following formula to retrieve my date: =INDEX(Misc!$A:$C,MATCH(C2,Misc!$A:$A,0),2) The problem with this is it will retrieve the "N" for the first occurrence of Adams and Reese. If I mailed a letter to Ms. Debora Hunt, I want the 12/01/05 date to populate the MAILED column. hgopp99 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match from multiple columns
You've got an active thread in .misc.
Which thread should be used for responses? hgopp99 wrote: I did not understand your function. I may need for detain explanation. Before that,I would like to present another example of the problem; a specific example that we can use to help determine the correct course. Worksheet:Misc Company Date1 Date2 Title First Last American Cast Iron 1/11/2006 N Mr. Stephen Calgore Associated Grocers 1/10/2006 1/01/2006 Mr. Ron Burke Associated Grocers 1/15/2006 1/01/2006 Ms. Alora Reed Associated Grocers 1/20/2006 1/01/2006 Mr. David Vaughn Worksheet:Client Date1 Date2 Company Title First Last - - American Cast Iron Mr. Stephen Calgore - - Associated Grocers Mr. Ron Burke - - Associated Grocers Ms. Alora Reed - - Associated Grocers Mr. David Vaughn I am trying to retreive the date from Misc worksheet and place it in the Client worksheet. I have been using: =INDEX(Misc!$A:$C,MATCH(C7,Misc!$A:$A,0),3) but this works when there is only one instance of a company or all the dates for the company are the same. However, in the example above, there are three different dates for Associate Groces and I would like to know each one. I did not understand the previous reply and hope this will help. Please provide specifics, to make learning a little easier. Thank you. -- hgopp99 "RagDyeR" wrote: According to your example, C2 on the Client sheet is "Title". Just changing that to B2, to reference "Company" Try this *array* formula in : =INDEX(Misc!$A$1:$C$20,MATCH(1,(Misc!$A$1:$A$20=B2 )*(Misc!$B$1:$B$20<"N"),0 ),2) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "hgopp99" wrote in message ... I have the following scenario. -- Client Worksheet MAILED COMPANY TITLE FIRST LAST Xxxxxxxx Acton Flooring Inc. Ms. Marsha Acton Xxxxxxxx AcuTech Ms. Linda Dendy Xxxxxxxx Adams and Reese Mr. David Hunt Xxxxxxxx Adams and Reese Ms. Deborah Hunt Misc Worksheet COMPANY DATE1 DATE2 TITLE FIRST LAST Acton Flooring Inc. 12/01/05 N Ms. Marsha Acton AcuTech 12/01/05 N Ms. Linda Dendy Adams and Reese N N Mr. David Hunt Adams and Reese 12/01/05 N Ms. Debora Hunt I use the following formula to retrieve my date: =INDEX(Misc!$A:$C,MATCH(C2,Misc!$A:$A,0),2) The problem with this is it will retrieve the "N" for the first occurrence of Adams and Reese. If I mailed a letter to Ms. Debora Hunt, I want the 12/01/05 date to populate the MAILED column. hgopp99 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match from multiple columns
I apologize for not handling the forum well, but I am doing all I can to resolve my issue. For now, I have tried Dave Peterson and RagDyeR's replies with no success. If I am to do some substitutions, I need further explanation. -- hgopp99 ------------------------------------------------------------------------ hgopp99's Profile: http://www.excelforum.com/member.php...o&userid=30699 View this thread: http://www.excelforum.com/showthread...hreadid=501669 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match from multiple columns
I think you'll get much better responses if you ask a specific question.
hgopp99 wrote: I apologize for not handling the forum well, but I am doing all I can to resolve my issue. For now, I have tried Dave Peterson and RagDyeR's replies with no success. If I am to do some substitutions, I need further explanation. -- hgopp99 ------------------------------------------------------------------------ hgopp99's Profile: http://www.excelforum.com/member.php...o&userid=30699 View this thread: http://www.excelforum.com/showthread...hreadid=501669 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| making multiple columns when printing long datasheet | Excel Discussion (Misc queries) | |||
| Justify text across multiple columns | Excel Discussion (Misc queries) | |||
| drop down list multiple columns | Excel Discussion (Misc queries) | |||
| Pivot Table combining multiple columns | Excel Discussion (Misc queries) | |||
| HOW TO MATCH MULTIPLE COLUMNS WITH OR WITHOUT GAPS IN eXCEL ? | Excel Worksheet Functions | |||
Linear Mode
