Index/Match from multiple columns

[hgopp99]

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

 

[RagDyeR]

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!
=====================================================

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

 

[hgopp99]

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.

 

[Dave Peterson]

You've got an active thread in .misc.

Which thread should be used for responses?

 

[hgopp99]

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.

 

[Dave Peterson]

I think you'll get much better responses if you ask a specific question.