INDEXとMATCHで別シートの関数を引く方法

質問

定義シートに支払方法のリストが作ってあり、データベースとなる入力シートで支払い方法をA列にリストから選択で入力、B列に計上日を入力すると、C列に支払方法に合わせた支払い予定日が自動で入るようにしたいと思い、以下のようにしました。
入力シート　B列の$B$2セル以下は「計上日」という名前を定義。
　　　　　　C列には=INDEX(支払予定日関数,MATCH(A2,支払方法,0),2)を設定し、定義シートB列の関数を引いて来る

定義シート　下記の$A$2:$B$4には「支払予定日関数」の名前を定義
　　　　　　$A$2:$A$4には「支払方法」の名前を定義
　　　　　　ほかに祝日一覧を作り「祝日」の名前を付けてあります。
　　　　　　　A列　　　　　　B列
　　　　　2　現金　　　　　=計上日
　　　　　3　普通預金　　　=計上日
　　　　　4　カード　　　　=IF(DAY(計上日)<16,WORKDAY(WORKDAY(DATE(YEAR(計上日),MONTH(計上日)+1,10),-1,祝日),1,祝日),WORKDAY(WORKDAY(DATE(YEAR(計上日),MONTH(計上日)+2,10),-1,祝日),1,祝日))
ところが、うまくいきません。「支払い予定日関数」の行列を入れ替えたり、B列の関数の前に「”」や「’」をつけてみたりしたけど、たとえば、6行目の計上日の答えをほしいのに、カードであれば、計上日列トップから数えて3行目の計上日に対応する日付が返るのです。「支払い予定日関数」でカードが3行目だからですね。何を直したらいいのでしょうか？

回答No.5

cafe_au_lait · ベストアンサー

No.2,3,4です。
「○○日締め翌○○日払い」がいろいろあるのでしょうか。
だとすると、表にすべきはこの日付ですね。

「支払予定日」(名前定義します)
カード名 締め日 支払日
カードA 15 10
カードB 25 20
・・・

=IF(OR(A2="現金",A2="普通預金"),B2,WORKDAY(EOMONTH(B2-VLOOKUP(A2,支払予定日,2,0),1)+VLOOKUP(A2,支払予定日,3,0)-1,1))

条件に応じて工夫してみてください。

お礼

あっ、そうか！！日付をVLOOKUPで引っ張ればいいんですね！ありがとうございます。大変助かりました。

回答No.4

cafe_au_lait

何度もすみません。

EOMONTH(B2-15,2)
は、
EOMONTH(B2-15,1)
の誤りです。

回答No.3

cafe_au_lait

蛇足ですが・・・。

セルの参照は、名前定義をしたとしても、あくまでも値の参照です。「支払予定日関数」が参照元の計上日に応じて値を変化させることはありません。

お礼

そういうところが、文系・独学・自己流であてずっぽうに体で覚えてきた私のような人間は苦手なんです。テレビをたたいて直してきたやり方でエクセルいじってます。勉強になりました。ありがとうございました。

回答No.2

cafe_au_lait

定義シートは使いません。
入力シートのC2に以下の式を入力してください。

=IF(A2<>"カード",B2,WORKDAY(EOMONTH(B2-15,2)+9,1,祝日))

条件を読み違えていましたらごめんなさい。

補足

例えばあるカードの支払いが「15日締め翌10日払い」でしたので、cafe_au_laitさんの式を一部直し
=IF(A2<>"カード",B2,WORKDAY(EOMONTH(B2-15,1)+9,1,祝日))
にしたら、なるほど計上日に合わせた引き落とし日が返されました。一歩前進です。
ありがとうございます。
問題は、私がたくさんカードを持っていることです。それで支払い予定日関数の表を作り、カードの種類に合わせた関数をINDEXで引っ張ってこられないかな、と思ったのです。

回答No.1

zap35

＞B列の$B$2セル以下は「計上日」という名前を定義。
すると名前定義の「計上日」は複数のセル範囲なのですか？
DAY関数、YEAR関数、MONTH関数は複数のセル範囲は指定できませんよ
　DAY(B1)　や　YEAR(C2)
のように一つのセルを指定してください。

試しに「4　カード」の式で「計上日」を単一のセル（例えばA1）に変更してみてください。A1の日付を変更すると正しい結果が得られませんか？

またここにも「計上日」が現れますね。これは質問と何か関係ありますか？
＞　2　現金　　　　　=計上日
＞　3　普通預金　　　=計上日
名前定義の「計上日」だとしたら、そんな式はありません。#VALUE!エラーになるはずです。

質問に本当に聞きたいこと以外のことを色々書かれると回答する側は混乱しますので、ポイントは絞って質問する方がよいですよ

お礼

おかげさまで、何とかなりそうです。勉強不足・理解不足でお恥ずかしい限り。アドバイスありがとうございました。

補足

うまく説明できず、申し訳ありません。「計上日」という名前は列のトップのタイトルを除く列全体につけたものですが、「=IF(DAY(計上日)<16,WORKDAY(WORKDAY(DATE(YEAR(計上日),MONTH(計上日)+1,10),-1,祝日),1,祝日),WORKDAY(WORKDAY(DATE(YEAR(計上日),MONTH(計上日)+2,10),-1,祝日),1,祝日))」というカードの引き落とし予定日の関数を入力シートの支払い予定日に直接張り付けてみたところ、ちゃんと計上日に合わせた引き落とし日が返りました。cafe_au_laitさんの関数でもOKでした。問題は、入力シートの計上日の隣に直接IF関数で設定するには、私のカードの種類が多すぎることなのです。それで、別に引き落とし日一覧を作り、カードの種類に応じて設定した関数をINDEXで引っ張れないかなと思ったのです。