Returning Multiple Values In Single Cell

[jose]

Hello All,

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.

Thanks,

Jose

 

[Frank Kabel]

Hi
one way (this requires VBA):
- download Alan Beban's array functions: http://home.pacbell.net/beban
- download the free add-in Morefunc.xll
(http://longre.free.fr/english/)

Now use the following formula:
=MCONCAT(VLOOKUPS(DATE(2004,3,17),A1:B100,2,0),", ")
assuming the date is in column A and the description in column B

 

[Alan Beban]

Thanks for the reference to the VLookups function; but one note: unlike
the built-in VLOOKUP function, VLookups takes only 3 arguments.

Alan Beban

 

[Frank Kabel]

Hi Alan
sorry, my fault. Should have read your description