エクセルで複数の条件に基づき一覧表から数値を返す

質問

初めて質問させていただきます。
エクセルで二つの条件に基づいて一覧表から合致する値を返したいのですがうまくいきません。
Sheet１
　　A　　　B　　C
１　４　　7-9　1017
２　５　　7-9　1137
Sheet２
　　A　　　B　　　C　　　D　　　E　　　　　
１　　　　2-3　　3-5　　5-7　　7-9
２　１　　　229 　301 　 576 　 660
３　２ 　300 　400 　678 　776
４　３ 　 371 　499 　779 　 897
５　４ 　442 　598 　880 　 1017
６　５　 　513 　697 　982 　1137

このような表があった場合にSheet１のC1をA1とB1の入力値に応じてSheet2の表を参照して自動表示させたいのです。参照したい数値はSheet2のB2:E6のいずれかです。

Sheet１A１が４、B1が7-9の時はSheet2のE5の数値を返すといった具合です。

自分で作った数式もあるのですが、途中で［引数が多すぎます］といったメッセージが出てイマイチ使えませんでした。参考になるかは分かりませんが載せておきます。
Sheet１
C1=IF(AND(A1=5,B2="7-9"),Sheet2!E6,IF(AND(A1=4,B2="7-9"),Sheet2!E5,IF(AND(A1=3,B1="7-9"),Sheet2!E4,・・・この後も全ての数値をカバーしたかったのですが、４つくらいしか出来ませんでした。

うまくまとめきれなくて申し訳ありませんが、回答をよろしくお願いします。

回答No.3

tom04 · ベストアンサー

No.1です！
補足を読ませていただきました。

前回は余計なお世話を焼いていたようで・・・
7-9　のような表示に関しては文字列等々関係なく
Sheet1・Sheet2にそのように表示されている前提で

↓の画像で
Sheet1のE1セルに
=VLOOKUP(A1,Sheet2!A$2:E$6,MATCH(D1,Sheet2!A$1:E$1,0))
という数式を入れオートフィルで下へコピーしています。

この場合空白セルを参照してしまうとエラー表示になりますので、
前回IF関数を使って単にエラー処理していただけです。
今回の質問で大切なのはこのVLOOKUP関数です。
COUNTBLANK関数はあまり気にされなくても良いと思います。
=IF(OR(A1="",D1=""),"",VLOOKUP(A1,Sheet2!A$2:E$6,MATCH(D1,Sheet2!A$1:E$1,0)))
のような数式にしても同様の結果になると思います。

ただ
＞途中で［引数が多すぎます］といったメッセージが出て・・・
の部分が気にかかりますが、

とりあえずこの画面から数式をコピー＆ペーストして試してみてください。m(_ _)m

補足

親切に対応していただきありがとうございます。
教えて頂いた式を入力したのですが、A1とD1に表示があるにも関わらず#N/Aというエラーが出ます。
実際の表では1列目が開始時間等の項目を入れて2列目から数値を入れるような表なのですが、そのせいでしょうか!?

回答No.6

tom04

何度もごめんなさい。

No.5で誤記がありました。

＞VLOOKUP・INDEX関数の「TRUE」型
は
＞VLOOKUP・MATCH関数の「FALSE」型

の間違いです。m(_ _)m

お礼

拙い質問に対して何度も回答いただき本当にありがとうございました。
No.4に補足した後、これまで使っていた表のSheet2（一覧表）を試しに作った表のものに差し替えたらこれまでの表でも問題なく結果が出ました。（原因は不明ですが）参照した表の問題だったようです。
なおベストアンサーは今回の疑問を解決するにあたって使用した数式が記載されているNo.3を選ばせていただきました。

回答No.5

tom04

またまた参上です。

No.4の補足で・・・

関数に関しては問題なく機能しているみたいですね！
そこでやっぱり気になっていたのですが、
No.1で書いたように「2-3」といったデータは日付として判断されてしまいます。

Sheet1・及びSheet2のセルをクリックしてみてください。
数式バーにはどのように表示されているでしょうか？
仮に5桁数値だと日付（シリアル値）です。
両Sheetとも同じデータで表示されているのであれば問題なく希望するデータが表示されるはずです。

ただどちらか一方が　「2-3」のように文字列で表示され、別Sheetがシリアル値で表示される場合は
全く別データとなりますので、当然表示されるのはエラーになります。

Excelのデータとして　2-3　とか　2/3　のように入力してしまうと
Excel的には日付（2月3日）として処理し、シリアル値のデータになります。
（おせっかい機能の一つかもしれません）
基本的にはこのようなデータ入力はオススメできません。

どうしても　2-3　のようにしたい場合は
2～3のようにすれば文字列としてそのまま表示されます。

今回はVLOOKUP・INDEX関数の「TRUE」型で対応できる質問ですので、
完全一致でないとエラーになります。

※　回答ではなく、エラーになる原因と考えられる要因だけのアドバイスですが
長々と失礼しました。m(_ _)m

回答No.4

tom04

続けてお邪魔します。

ん～～～

No/2さんの補足等を読ませていた出して、
気になるコトがあります。

数式は数式バーに直接手入力されているのですか？

もしそうであれば、慣れるまでは「関数の引数」ダイアログボックスを使用する方法をオススメします。

数式を入れたいセルを選択　→　数式バー左側の「fx」をクリック　→　「関数の挿入」ダイアログが出ます
→　「関数の種類」で「すべて表示」を選択　→　「関数名」の欄の中でどこでも良いので一度クリック　
→　使用したい関数の頭文字（今回は「V」を押下　→　VLOOKUPを選択　→　OK　
これで↓の画像のような「関数の引数」ダイアログボックスが表示されます。

「検索値」にカーソルを移動　→　A1　を選択　→　「範囲」にカーソル　→　Sheet2のA2～E6を範囲指定　→　「列番号」にカーソル　→　画面左上に「VLOOKUP」と表示されている右側の▼　→
MATCH関数を選択（MATCH関数が表示されない場合は「その他の関数」を選択し、MATCH関数ダイアログを表示）
→　MATCH関数のダイアログが出ますので同様にMATCH関数を仕上げます。

VLOOKUP関数・MATCH関数等の詳細についてはネットで検索してみてください。

このようにマウスでセルを指定するだけで「Sheet2！」のように勝手にExcelの方で表示してくれます。

※　「$」マークに関しては「絶対参照・相対参照・複合参照」などでこれも検索してみてください。

尚、関数の引数ダイアログを使用して数式を仕上げている場合は無視してください。m(_ _)m

補足

色々な打開策を考えていただき本当に感謝しております。
「関数の引数」ダイアログボックスも試してみましたがうまくいきません。というより新規ブックにtom04様と同じ表を作り数式をコピペしたら成功しました。その後、行や列・参照表の設定など限りなくこれまでの表に近づけたのですが、全く問題ありません。
つまりこれまで作っていた表でのみエラーが表示されるのです。これはもう以前の表はあきらめたほうがいいのでしょうか・・・

回答No.2

KURUMITO

シート1のC1セルには次の式を入力して下方にドラッグコピーします。

=IF(A1="","",INDEX(Sheet2!A:E,MATCH(A1,Sheet2!A:A,0),MATCH(B1,Sheet2!$1:$1,0)))

補足

回答ありがとうございます。

確認ですが、A:E、A:A、$1:$1はSheet2のどこを参照したらいいのでしょうか？

回答No.1

tom04

こんばんは！
質問にあるような「2-3」といったデータはExcelでは日付と判断してしまいますので、
便宜上↓の画像のようにA～Dとしてみました。

一例です。

画像でSheet1のC2セルに

=IF(COUNTBLANK(A2:B2),"",VLOOKUP(A2,Sheet2!$A$2:$E$6,MATCH(B2,Sheet2!$A$1:$E$1,0)))

という数式を入れオートフィルで下へコピーしています。
他の方法としてはINDEX関数も使用できます。

参考になりますかね？m(_ _)m

補足

回答ありがとうございます。
さっそく入力したのですが、多すぎる引数が入力されている旨のメッセージが出ました。

COUNTBLANKは初めて見る関数なのですがセルが連続していなければ使えないのですか？
最初の質問のsheet1の表は最低限のもので本来はA1とB1の項目の間に2つ空きがあります。
もう少し詳しく書くと
　　A　　B　　　C　　　　D　　　　F
1　4　9:00　16:30　　7-9　　　1017
となります。つまりB1の項目はD1になり二つの条件はA1とD1に当てはまるものとなります。
ちなみにB1とC1の項目は開始時間と終了時間でD1はそれに応じて7-9と自動表示される計算式を入れております（B1とC1の時間は7時間30分なのでD1は7-9（7時間以上9時間以内）と表示されます。）

正確な情報をうまくお伝えできず申し訳ありませんでした。