エクセル VLOOKUPで複数抽出

質問

エクセルで検索時の近似値を表示する際に同じ行の値を同時に表示させたい。
なおかつ近似値を検索値よりも大きいものに指定する場合の方法を知りたいのです。
画像のように検索値を「120」とした場合に赤枠で囲んだ値を表示させたいということです。
もし画像のように「タテ」の値（128）のみの検索ではなく、黄色で塗りつぶしたセルのように
「ヨコ」の値（128）も同時に表示する方法があればあわせてご教授願います。

回答No.2

kagakusuki · ベストアンサー

今仮に、「インチ」と入力されているセルがSheet1のA1セルであり、Sheet2のB1セルに検索値を入力すると、Sheet2のA列～C列の4行目以下に抽出結果を表示させるものとします。
　又、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA1セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(Sheet2!$B$1),COUNT(Sheet1!$B:$C)),MIN(IF(COUNTIF(Sheet1!$B:$C,"<="&Sheet2!$B$1),Sheet2!$B$1-SMALL(Sheet1!$B:$C,COUNTIF(Sheet1!$B:$C,"<="&Sheet2!$B$1)),9E+307),IF(COUNTIF(Sheet1!$B:$C,">="&Sheet2!$B$1),LARGE(Sheet1!$B:$C,COUNTIF(Sheet1!$B:$C,">="&Sheet2!$B$1))-Sheet2!$B$1,9E+307))&"","")

　次に、Sheet3のA2セルに次の関数を入力して下さい。

=IF(AND($A$1<>"",INDEX(Sheet1!$A:$A,ROW())<>"",COUNT(INDEX(Sheet1!$B:$B,ROW()),INDEX(Sheet1!$C:$C,ROW()))=2),IF(OR(ABS(INDEX(Sheet1!$B:$B,ROW())-Sheet2!$B$1)-$A$1=0,ABS(INDEX(Sheet1!$C:$C,ROW())-Sheet2!$B$1)-$A$1=0),ROW(),""),"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

　次に、Sheet2のA4セルに次の関数を入力して下さい。

=IF(ROWS($4:4)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$C,SMALL(Sheet3!$A:$A,ROWS($4:4)),COLUMNS($A:A)))

　次に、Sheet2のA4セルをコピーして、Sheet2のB4～C4の範囲に貼り付けて下さい。
　次に、Sheet2のA4～C4の範囲をコピーして、同じ列範囲の5行目以下に貼り付けて下さい。

　以上で準備は完了で、後はSheet2のB1セルに検索値を入力しますと、検索値に最も近い値が入力されている行のデータが全て表示されます。

お礼

ご回答ありがとうございます。
回答どおりにやってみたら見事に完成しました！
Sheet1に列（検索する必要のない項目）が増えた場合(D,Eなど)はSheet2のA4セルに入力する関数を

=IF(ROWS($4:4)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$E,SMALL(Sheet3!$A:$A,ROWS($4:4)),COLUMNS($A:A)))

というようにC→Eと変えてから同じようにコピーしていけばいいのですね？
（一応実験したところその方法で大丈夫でした）

本当にありがとうございます。助かりました！

回答No.3

keithin

添付図：
簡単のため、Ｇ１セルに
=IF(E1>MAX(B:C),"N/A",LARGE(B:C,COUNTIF(B:C,">="&E1)))
と記入。１２８を含むデータを探す事にします

Ｅ２に
=INDEX(A:A,SMALL(IF(($B$1:$B$99=$G$1)+($C$1:$C$99=$G$1),ROW($A$1:$A$99),9999),ROW(E1)))&""
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力
以下コピー

Ｆ２に
=IF($E2="","",VLOOKUP($E2,$A:$C,COLUMN(B2),FALSE))
と記入、右に下にコピーして完成。

Ｇ１を準備するのがどーしてもイヤなら、もちろんＥ２の式にＧ１の内容を組み込んで作成しても構いません。
まず回答のその通りにあなたもまっさらのエクセルで作成し、出来ることを確認してから改めてあなたの実際に応用してください。

お礼

ご回答ありがとうございます。
おっしゃるとおりに再現したつもりなのですがうまくいきませんでした。

>必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力
　以下コピー

>右に下にコピーして完成。

上記の「コピー」のやり方を間違えているか、「右に下に」の意味を取り違えて
しまっているのだと思います。よろしければ後学のためにこの部分をもう少し詳しくご教授願えますか？

回答No.1

aokii

近似値を表示する際に同じ行の値を同時に表示する場合の方法
同時に表示する列に=VLOOKUP(A7,A1:C4,3,FALSE)

近似値を検索値よりも大きいものに指定する場合の方法
同時に表示する列に=VLOOKUP(A7,A1:C4,3)

お礼

早速のご回答をありがとうございます。
ただ、初心者の私には教授頂いた方法を再現することが出来ませんでした。
知識としてお答えをメモしておきます、ありがとうございました。