Returning Multiple Values in a Single Cell

[jose]

Hello,

I posted this same question a couple of weeks ago but forgot to
mention a key point. My question was as follows:

I have read a few posts regarding this subject but am still somewhat
lost. I have a two sheet workbook (Excel 2000). On the first sheet
is a list of projects that my department will complete during the
year. A description of each project is given, a start date, an end
date, as well as which crew will be completing the work (Crew 1 or
Crew 2).

What I am trying to do on the second sheet is to be able to type in
any date, and have the description of the project on that date to be
returned. However, some days have multiple projects on them and I am
having problems returning both descriptions. Here is an example.

Activity A March 1 March 30 Crew 1
Activity B March 17 March 21 Crew 2
Activity C March 17 March 17 Crew 1

If I type in the date (on the second sheet) "March 17" and another
cell also on the second sheet denoting "Crew 1", I would like to see
"Activity A, Activity C" returned in an adjacent cell. I have
experimented with the INDEX, MATCH, and SMALL functions but am having
problems with them once I try and apply multiple criteria. Has anyone
ever encountered this?? Any help would be greatly appreciated.

I received a response (thank you by the way) to download an add-in off
the net. The key point I forgot is that I am working in a network
environment (at work) and the capabilities to download these types of
things have been disabled. I was just wondering if there is some sort
of long worksheet function that I could use which would be equivilant.
I could also have the output in a couple of cells if it is not
possible to put it in a single cell. Once again any help would be
greatly appreciated and sorry about forgetting to add that "key"
point.

Thanks

Jose

 

[Max]

One set-up which might bring you quite close to what you're after ..

Assume in Sheet1
-------------------------
Data is in cols A to D, from row1 down:

Activity A March 1 March 30 Crew 1
Activity B March 17 March 21 Crew 2
Activity C March 17 March 17 Crew 1

Put in E1:

=IF(AND(Sheet2!$A$1>=Sheet1!B1,Sheet2!$A$1<=Sheet1!C1,Sheet2!$B$1=Sheet1!D1)
,Sheet1!A1,"")

Put in F1: =IF(E1="","",ROW())

Select E1:F1 and copy down as many rows as there is data

In Sheet2
-------------
In A1 and B1 will be entered
the "Date" and "Crew", e.g.:

A1: March 17
B1: Crew 1

Put in C1:

=IF(ISERROR(MATCH(SMALL(Sheet1!F:F,ROW(A1)),Sheet1!F:F,0)),"",OFFSET(Sheet1!
$E$1,MATCH(SMALL(Sheet1!F:F,ROW(A1)),Sheet1!F:F,0)-1,))

Copy C1 down as many rows as there is data in Sheet1's col A

Col C will return all the Activities from Sheet1
satisfying the inputs in A1 and B1

For the sample data in Sheet1
and the sample inputs in A1 and B1 above,
you should see "Activity A" in C1 and "Activity C" in C2

 

[Max]

Clarification ..

Put in E1:

=IF(AND(Sheet2!$A$1>=Sheet1!B1,Sheet2!$A$1<=Sheet1!C1,Sheet2!
$B$1=Sheet1!D1),Sheet1!A1,"")

could be simplified to ..

Put in E1:

=IF(AND(Sheet2!$A$1>=B1,Sheet2!$A$1<=C1,Sheet2!$B$1=D1),A1,"")