lookup multiple data

[Guest]

HI,
i have a table that looks like this:

Name step 1 step 2 step 3 step 4 step 5 step 6
A 9-Sep-06 9-Aug-06 10-Oct-06 1-Nov-06 10-Oct-06 1-Dec-06
B 10-Oct-06 1-Dec-06 6-Aug-06 10-Oct-06 1-Jan-07 10-Oct-06


On another sheet, i would like to show what steps will be completed in
October, Nov, etc for each item without havign to renter the information

Name Oct-06 Nov-06 Dec-06
a Step 3 Step 4 Step 6
Step 5 -

b step 1 step 2
step 4
Step 6

Unfortunately, the steps are not sequential, they can happen in any order.
Additionally, there are about 40 steps so a series of IF statements is not
possible.

Thanks Emma

 

[Herbert Seidenberg]

I reduced the step labels to just numbers.
Name the rows (Steps, ADate, BDate, YearS) with
Insert > Name > Create > Left Column
Use 'Bottom Row' for the column name NameA.
Arrange your input/output data like this:

Steps 1 2 3 4 5 6
ADate 09/09/06 08/09/06 10/10/06 11/01/06 10/10/06 12/01/06
Bdate 10/10/06 12/01/06 08/06/06 10/10/06 01/01/07 10/10/06

YearS 07/01/06 08/01/06 09/01/06 10/01/06 11/01/06 12/01/06 01/01/07
ADate 0 2 1 5 4 6 0
0 0 0 3 0 0 0
0 0 0 0 0 0 0

Bdate 0 3 0 6 0 2 5
0 0 0 4 0 0 0
0 0 0 1 0 0 0
NameA

Use R1C1 to enter formulas. Uncheck R1C1 thereafter.
Enter the starting date in the first cell of YearS.
The next cells in YearS have the formula
=EDATE(YearS C[-1],1)
Below the first cell of YearS, select as many cells as you need,
(in this example only 3 are needed), and enter this array formula:
=LARGE(Steps*(INDIRECT(NameA R)>=YearS C)*
(INDIRECT(NameA R)<EDATE(YearS C,1)),TRANSPOSE(Steps))
Copy across the 3 cells for each NameA to cover calendar.