ifとvlookupの組み合わせ？

お世話になります。

シートAとシートBの結果を参照して、シートCに結果を表示させたいのですがうまくいかず、、どなたかお知恵を貸していただけないでしょうか。

シートA～Cの列の項目は全て同じです。やりたいことは、まず、Aのシートの特定のデータを参照し、そこが"合格"ならシートCに"合格"を、もし"不合格"ならシートBを参照し、"合格"ならシートCに"合格"と表示、シートBでも"不合格"ならシートCに"不合格"と表示されるようにしたいです。

イメージとしては、

【シートA】1月結果

No 生徒名　　国語　　数学　　英語　　理科　　社会 ・・・・・・・
01 鈴木　　　不合　　不合　　不合
02　田中　　　不合　　合格　　合格
03　木村　　　合格　　不合　　合格

【シートB】3月結果

No 生徒名　　国語　　数学　　英語　　理科　　社会 ・・・・・・・
01 鈴木　　　合格　　合格　　不合
02　田中　　　不合　　合格　　合格
04　山田　　　不合　　不合　　不合

【シートC】集計

No 生徒名　　国語　　数学　　英語　　理科　　社会 ・・・・・・・
01 鈴木　　　合格　　合格　　不合
02　田中　　　不合　　合格　　合格
04　山田　　　合格　　不合　　合格

上記のように、1月結果には存在するけれども、3月には退会等によって削除された生徒分については、シートCへの反映は必要ないのですが、
逆に、シートAの時にはいなかったけれども、シートBの時、入会によって増えた生徒分はシートCへBの結果を反映させたいです。

生徒に割り当てられたNoは全て固有の番号ですので、入退会による使い回しや変動はありません。

IfとVlookupでできるかと思って、色々考えてみたのですが、全くうまくいかず、ご相談させていただきました次第です。
エクセル初心者で勉強中のため、IfとVlookupに固執するのではなく、（といいますか、そもそもIFとVlookupでできるものなのかどうかもわかっていませんが）
上記のような結果が出せる方法があれば、色々ご教授いただければ幸いです。

どうぞよろしくお願いいたします。

No.1 回答者： nattocurry 回答日時： 2011/04/21 17:32

シートAに名前があってもシートBに名前が無かったら集計しない。

シートAに名前が無くてもシートBに名前があったら集計する。
ということは、シートCとシートBの名前は同じになるということですね。：

1月結果がSheet1、3月結果がSheet2、集計がSheet3、だったら、

Sheet3のセルA2に
=Sheet2!A2

Sheet3のセルB2に
=Sheet2!B2

Sheet3のセルC2に
=IF(ISNA(MATCH($A2,Sheet1!$A:$A,0)),Sheet2!C2,IF(AND(VLOOKUP(Sheet3!$A2,Sheet1!$A:$E,COLUMN(),0)="不合",Sheet2!C2="不合"),"不合","合格"))

検証用に、英語までの表を使ったので、表のサイズは$A:$Eになってますが、理科と社会までなら$A:$G、それよりも大きいのであれば、それに合わせたサイズに直してください。

Sheet3のセルD2以右はC2をコピペ

2行目が埋まったら、2行目全体を3行目以下にコピペ。

No.2 回答者： mu2011 回答日時： 2011/04/21 17:54

一例です。

シートＡをSheet1、シートＢをSheet2として、「ＮＯ」をキーにシートＡに無い場合は、シートＢから設定、これ以外は両シートの状況で合格、不合の文字列を設定しています。
シートＣのC2に以下の数式を設定、縦横にコピーして下さい。
=IF(COUNTIF(Sheet1!$A:$A,$A2),IF((VLOOKUP($A2,Sheet2!$A:$Z,COLUMN(C1),FALSE)="合格")+(VLOOKUP($A2,Sheet1!$A:$Z,COLUMN(C1),FALSE)="合格"),"合格","不合"),VLOOKUP($A2,Sheet2!$A:$Z,COLUMN(C1),FALSE))