I need to match multiple columns before returning a value
 

I have a spreadsheet with multiple worksheets. I would like to return a date
from one worksheet based on the name of a company provided as search
criteria. I have been using a combination of INDEX and MATCH and it is
working fine except when there are multiple instances of the company name.
Then I need to look at first and last names of employees within the company
to distinguish which person I am in need of. Most often, I will have a
single occurence and this will not be a problem, but I would like to know a
correct way of handling my discrepancies as the results are not correct.
Thank you.
--
hgopp99

I need to match multiple columns before returning a value
 

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

hgopp99 wrote:

I have a spreadsheet with multiple worksheets. I would like to return a date
from one worksheet based on the name of a company provided as search
criteria. I have been using a combination of INDEX and MATCH and it is
working fine except when there are multiple instances of the company name.
Then I need to look at first and last names of employees within the company
to distinguish which person I am in need of. Most often, I will have a
single occurence and this will not be a problem, but I would like to know a
correct way of handling my discrepancies as the results are not correct.
Thank you.
--
hgopp99

--

Dave Peterson

I need to match multiple columns before returning a value
 

I did not understand the reply. I am going to place an example here which
may help me when reading a reply. Scroll till you see my name.


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.

Thank you.

HOward




--
hgopp99


"Dave Peterson" wrote:

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

hgopp99 wrote:

I have a spreadsheet with multiple worksheets. I would like to return a date
from one worksheet based on the name of a company provided as search
criteria. I have been using a combination of INDEX and MATCH and it is
working fine except when there are multiple instances of the company name.
Then I need to look at first and last names of employees within the company
to distinguish which person I am in need of. Most often, I will have a
single occurence and this will not be a problem, but I would like to know a
correct way of handling my discrepancies as the results are not correct.
Thank you.
--
hgopp99

--

Dave Peterson