software

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

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

--

Dave Peterson

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
MAILEDCOMPANYTITLEFIRSTLAST
XxxxxxxxActon Flooring Inc.Ms.MarshaActon
XxxxxxxxAcuTech Ms.LindaDendy
XxxxxxxxAdams and ReeseMr.DavidHunt
XxxxxxxxAdams and ReeseMs.Deborah HuntMisc Worksheet
COMPANYDATE1 DATE2 TITLEFIRSTLAST
Acton Flooring Inc.12/01/05NMs.MarshaActon
AcuTech 12/01/05NMs.LindaDendy
Adams and ReeseNNMr.DavidHunt
Adams and Reese12/01/05NMs.DeboraHuntI 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 ???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

--
hgopp99quot;Dave Petersonquot; wrote:

gt; You can use this kind of syntax:
gt;
gt; =index(othersheet!$c$1:$c$100,
gt; match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
gt; (one cell)
gt;
gt; This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
gt; correctly, excel will wrap curly brackets {} around your formula. (don't type
gt; them yourself.)
gt;
gt; Adjust the range to match--but you can't use the whole column.
gt;
gt; hgopp99 wrote:
gt; gt;
gt; gt; I have a spreadsheet with multiple worksheets. I would like to return a date
gt; gt; from one worksheet based on the name of a company provided as search
gt; gt; criteria. I have been using a combination of INDEX and MATCH and it is
gt; gt; working fine except when there are multiple instances of the company name.
gt; gt; Then I need to look at first and last names of employees within the company
gt; gt; to distinguish which person I am in need of. Most often, I will have a
gt; gt; single occurence and this will not be a problem, but I would like to know a
gt; gt; correct way of handling my discrepancies as the results are not correct.
gt; gt; Thank you.
gt; gt; --
gt; gt; hgopp99
gt;
gt; --
gt;
gt; Dave Peterson
gt;