2つ条件である文字の値を参照したい

質問

いつもお世話になります
Excel 2010 win7です
入室　退出の管理です

例
1　リストで選択
B4 C4 B5 C5 B15 C15 B16 C16
E5 E6 E15 E16

2　手動入力
D4 D5 D15 D16
　　　　　　入室日　退出日(F4 F5)
田中　勝　1/5　　　1/10
　北　和夫　1/10　　　1/15
　佐藤　真一1/10　　　1/20

シート数(フォマットは同じ)は各月の枚数です

　ご指導を仰ぎたいのは、
入退室情報を記入して(図参照)

例えば　1月10日のシートで
1　居室が空室の時はそのままに　※この場合は　「佐藤　真一」
D5(氏名)E5(入)を入力すれば
C30(入)　D30(氏名) に文字の値を参照する数式
2　当日まで入室者が居るとき　※この場合は　「北　和夫」
D4(氏名) E4(入)を入力すれば、普通は前の1のようにH25 I25に入れられるのですが
当日迄「田中　勝」が入室していて「北　和夫」は一時待機の形になります
それ故H35 I35になるのでそのような時の数式はどのようにすればいいです
※また日が1月11日のシートに正規のH25 I25に変更される数式はあるでしょうか

誠に恐れ入りますがどんな数式を適用する必要なのか分からず丸投げ的で申し訳ありませんがなんとかお力添えいただけないでしょうか

回答No.3

nobu_suzuki1

何度もすいません、１で回答したものですがもう一箇所訂正箇所がありました。

=IF(ISERROR(INDIRECT("E"&MATCH(G25,$C$15:$C$22,FALSE))),IF(ISERROR(INDIRECT("E" & MATCH(G25,$C$4:$C$12,FALSE))),"",INDIRECT("E" & MATCH(G25,$C$4:$C$12,FALSE)+3)),INDIRECT("E"&MATCH(G25,$C$15:$C$22,FALSE)+14))

回答No.2

nobu_suzuki1

１で回答したものですが、一部訂正します。

H35
=if(H25<>"",if(iserror(indirect("E" & match(G35,$C$4:$C$12,false))),"",indirect("E" & match(G35,$C$4:$C$12,false)+3))
上記式をH34～必要なところまでオートフィル）

H25の比較条件を間違えていました。

回答No.1

nobu_suzuki1

認識があっているか微妙ですが、例えば以下のように数式を入れてみてはいかがですか。
H25
=IF(ISERROR(INDIRECT("E"&MATCH(G25,$C$15:$C$22,FALSE))),IF(ISERROR(INDIRECT("E" & MATCH(G25,$C$4:$C$12,FALSE))),"",INDIRECT("E" & MATCH(G25,$C$4:$C$12,FALSE)+3)),INDIRECT("E"&MATCH(C25,$C$15:$C$22,FALSE)+14))
上記式をH26～H34までオートフィル

H35
=if(H25="",if(iserror(indirect("E" & match(G35,$C$4:$C$12,false))),"",indirect("E" & match(G35,$C$4:$C$12,false)+3))
上記式をH34～必要なところまでオートフィル）

I25、I35については引数をいじるだけで出来ると思いますので割愛します。

これをすべてのシートに入れれば1/11のH25およびI25も問題なく動作するのではないかと思いますが。

補足

丸投げ的に投稿してご回答いただき誠にありがとうございます。
テストしてみました。
C4:E12に入力すると
　　B25:D37 G25:I37
には見事に参照してくれました。
ただし
C15:E22に入力すると
　　B25:D37 G25:I37
には　　#N/A が出ます。
例
C15　N107,D15 南,E15　退に入力して
C31 =IF(ISERROR(INDIRECT("E"&MATCH(B31,$C$15:$C$22,FALSE))),IF(ISERROR(INDIRECT("E" & MATCH(B31,$C$4:$C$12,FALSE))),"",INDIRECT("E" & MATCH(B31,$C$4:$C$12,FALSE)+3)),INDIRECT("E"&MATCH(G31,$C$15:$C$22,FALSE)+14))
D31 =IF(ISERROR(INDIRECT("D"&MATCH(B31,$C$15:$C$22,FALSE))),IF(ISERROR(INDIRECT("D" & MATCH(B31,$C$4:$C$12,FALSE))),"",INDIRECT("D" & MATCH(B31,$C$4:$C$12,FALSE)+3)),INDIRECT("D"&MATCH(H31,$C$15:$C$22,FALSE)+14))
の数式を入れていて　#N/A がでます。

数式がまちがっていますかな。
お手数ですがよろしくご指導いただけないでしょうか