index, match, total, ??

[cgreen]

Hi All,
I do not know how to write this formula and I’m hoping someone can help.

I want the formula to be in ‘Monthly Schedule column F’.

Look at the date in ‘Monthly Schedule B1’ and match to ‘Year Schedule Row 1’. Then I want it to match the ‘Monthly Schedule column B (PCN’s)’ to ‘Year Schedule column C (PCN)’ and give me the number (one row below) the matched PCN for the date in question. Example: N02JQ0 for Dec-04 = 22 ; N03BCZ for Dec-04 = 23 /// N02JQ0 for Jan-05 = 20 ; N03BCZ for Jan-05 = 18

Year Schedule.xls
	B	C	D	E	F
1	NOUN	PCN		Dec-04	Jan-05
2	T700Eng	N01JQ0	Production
3			Induction		1
4	T700Eng	N02JQ0	Production	6	19
5			Induction	22	20
6	T701CEng	N01BCZ	Production
7			Induction		1
8	T701CEng	N02BCZ	Production
9			Induction
10	T701CEng	N03BCZ	Production	4	11
11			Induction	23	18
Engines

Monthly Schedule.xls
	B	C	D	E	F
1	Jan-05
2	ProgramsDatabase
3	PCN'sforFY05	Name	Type		MonthSchdRequired
4	N01JQ0	T700Eng	OCMI0
5	N02JQ0	T700Eng	RecapA3
6	N01BCZ	T701CEng	OCMI0
7	N02BCZ	T701CEng	ConvC2
8	N03BCZ	T701CEng	RecapA3
Monthly Schedule

 

[Aladin Akyurek]

Book1
	B	C	D	E	F
1	Jan-05
2	ProgramsDatabase
3	PCN'sforFY05	Name	Type		MonthSchdRequired
4	N01JQ0	T700Eng	OCMI0		1
5	N02JQ0	T700Eng	RecapA3		20
6	N01BCZ	T701CEng	OCMI0		1
7	N02BCZ	T701CEng	ConvC2		0
8	N03BCZ	T701CEng	RecapA3		18
9
Monthly Schedule

Assuming that Dec-04 is custom-formatted 12/1/2004 and Jan-05 1/1/2005...

In F4 enter & copy down:

=INDEX(Engines!$E$2:$F$11,MATCH(B4,Engines!$C$2:$C$11,0)+1,MATCH($B$1,Engines!$E$1:$F$1,0))

 

[cgreen]

Beautiful piece of work. Thank you very much