lookup multiple data
 

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

lookup multiple data
 

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.