index, match, total, ??

cgreen

Active Member
Joined
Aug 14, 2002
Messages
291
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
BCDEF
1NOUNPCNDec-04Jan-05
2T700EngN01JQ0Production
3Induction1
4T700EngN02JQ0Production619
5Induction2220
6T701CEngN01BCZProduction
7Induction1
8T701CEngN02BCZProduction
9Induction
10T701CEngN03BCZProduction411
11Induction2318
Engines
Monthly Schedule.xls
BCDEF
1Jan-05
2ProgramsDatabase
3PCN'sforFY05NameTypeMonthSchdRequired
4N01JQ0T700EngOCMI0
5N02JQ0T700EngRecapA3
6N01BCZT701CEngOCMI0
7N02BCZT701CEngConvC2
8N03BCZT701CEngRecapA3
Monthly Schedule
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Book1
BCDEF
1Jan-05
2ProgramsDatabase
3PCN'sforFY05NameTypeMonthSchdRequired
4N01JQ0T700EngOCMI01
5N02JQ0T700EngRecapA320
6N01BCZT701CEngOCMI01
7N02BCZT701CEngConvC20
8N03BCZT701CEngRecapA318
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))
 

Forum statistics

Threads
1,148,269
Messages
5,745,782
Members
423,972
Latest member
franklins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top