Excelで範囲内にあるデータを引用して表示させる方法

質問

こんばんわ。いつもお世話になります。
また、皆様のお知恵を借りたくて質問させていただきます。
今回下記のような、2つのシートがあり、メインシートのA1のセルに任意の数値を入れるとサブシートに既にマスタとして入力されてある表のDセル、Eセルの間の範囲にある行のAセル、Bセル、Cセルの値をメインシートで表示をさせたいのですが良い方法はありますでしょうか。
例えば、メインシートA1セルに「135」の数値をいれるとサブシートのD、E各列から範囲に適応する行(130以上～150未満)のA3、B3、C3のセルの値をメインシートのB1、C1、D1のセルに表示させたいのです。
サブシートのデータ数は約50行程あります。
申し訳ありませんがよろしくお願いします。説明不足なところはその都度補足させていただきます。

メインシート(Sheet1)
A B C D
135 140 ウ c
107 100 ア a

サブシート(sheet2)
A B C D E
ア a 100 90以上 110未満
イ b 120 110 130
ウ c 140 130 150

シートがうまく表示できてませんが、よろしくです。

回答No.1

nihonjinn · ベストアンサー

Sheet1のA1セルに対応するサブシートのデータがある行の位置は
=MATCH(A1,Sheet2!D:D)
で出すことが出来ました。すなわちOFFSET関数を使うとするとSheet1のB1には
=OFFSET(Sheet2!$C$1,MATCH(A1,Sheet2!D:D)-1,0)
Sheet1のC1には
=OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!D:D)-1,0)
Sheet1のD1には
=OFFSET(Sheet2!$B$1,MATCH(A1,Sheet2!D:D)-1,0)
となります。

お礼

回答を頂きながら返事が遅くなってすいません。
あ、ありがとうございます!!　教えていただいた数式で見事に解決できました。本当にありがとうございます!
また、次回の際にもよろしくお願いします。

回答No.2

imogasi

ＶＬＯＯＫＵＰ関数の（第４引数）ＴＲＵＥ型で出来ると思います。第４引数を省略するとＴＲＵＥ型になります。
（１）Ｓｈｅｅｔ２のテーブル作成
90 ア ａ 100
110 イ ｂ 120
130 ウ ｃ 140
150 140
検査値は最左列に持ってくる。質問に記されたの順序を変えてます。
検査値は昇順に並べること。
（２）Ｓｈｅｅｔ１の
Ｂ１に=VLOOKUP(A1,Sheet2!$A$1:$D$4,4,TRUE)
Ｃ１に=VLOOKUP(A1,Sheet2!$A$1:$D$4,2,TRUE)
Ｄ１に=VLOOKUP(A1,Sheet2!$A$1:$D$4,3,TRUE)
を入れて下行へ複写する。
８９以下、１５０超の処理はポイントを説明し、簡単化するため考慮してません。
（３）検索しているやり方
一致する値が見つからない時は越えて次ぎの行まで行く。
１３５はないので１５０まで行く。
そして１行前の
130 ウ ｃ 140
を見て、あとは左から第何列目を取るかは、関数式の
第３引数で示されている列から取る。
検査値のある列が１と数えます、０ではありません。

お礼

回答を頂きながら返事が遅くなってすいません。
わぁ、VLOOKUP関数でも見事に出来ました!　本当に皆さんの知識には驚かされます。今回はSheet2のデータをそのまま使えるnihonjinnさんの数式を使わせて頂こうと思いますが、imogasiさんの方法も大変参考になりました。本当にありがとうございました。
また、次回の際もよろしくお願いします。