VLOOKUP関数の結果セルの右下のセルを表示したい

質問

VLOOKUP関数について、質問させてください。
現在使っているエクセルのブックのセルA1にVLOOKUP関数を使用し、そのVLOOKUPの「範囲」の部分に、別のブックの範囲を入力しました。
そしてその別ブックの中の、VLOOKUPの検索値に該当するセル（ここでは※とします）の値が、現在使っているブックのセルA1に表れるところまでは出来ました。
しかし、今度は現在のブックのセルA3に、別ブックの※セルを基準に、右方向に１つ・下方向に１つ移動したセルの値が自動的に表示されるような関数を出さなくてはならず、OFFSET関数やADDRESS関数など、色々試してみたのですが分かりません。

遅い時間に申し訳ありませんが、関数にお詳しい方は、どうか知恵をお貸しください。
よろしくお願いします。

回答No.4

tom04 · ベストアンサー

こんばんは！
すでに回答は出ていますので、
重複すると思いますが、
一例です。

↓の画像ではSheet1のA1セルに
C1セルを参照して2列目の一致するものを返すようにしています。

=VLOOKUP(C1,Sheet2!A1:C10,2,0)
という数式が入っています。

A3セルには
=OFFSET(INDEX(Sheet2!B1:B10,MATCH(C1,Sheet2!A1:A10,0)),1,1)
という数式を入れています。
結局A1セルはA#セルに関して、意味はなく
必要なくなってしまいますね！

尚、エラー処理はしていません。

以上、参考になれば幸いです。m(__)m

お礼

回答、本当にどうもありがとうございます。
本当は私がやらなくてはならない画像作成も、わざわざtom04さんがやってくださって、ありがたいのと申し訳ないのとで、とにかく感謝しております。
私でも分かるような図解と私の質問文を考慮してくださった画像作成のお陰で、問題は全て片付きました。
昨日からずっと悩んでいたので、とても嬉しいです。
質問文には「右に１・下に１」とだけ書いていて、「右に１・下に２」「右に２・下に１」と書くのを忘れてしまったのですが、式の中の「1,1」の数字を変更することで簡単に応用することが出来て、とても使える式なのがさらにありがたかったです。
tom04さんの回答文と画像は、OFFSET関数とINDEX関数の組み合わせの勉強にもなって、以後も役に立ちそうなので、プリントアウトして個人的にスクラップしておきたいと思います。

画像作成という手間を惜しまずお答えくださって、本当に本当にありがとうございました！

回答No.3

noname#204879

「別ブック」名を Other.xls とし、貴方が作成したVLOOKUP式の「範囲」の部分に range という名前を付けた場合の式を示しておきます。

=OFFSET(INDIRECT(CELL("address",Other.xls!range)),MATCH(検索値,OFFSET(INDIRECT(CELL("address",Other.xls!range)),,,ROWS(Other.xls!range),),0),n,,)

上式中の n はVLOOKUP式で指定した列番号と同じ数値にします。

貴方が質問文中に、検索値、「範囲」を含む VLOOKUP式の内容を具体的に記しておけば、CELL("address",Other.xls!range) なんてなまどろっこしいことをする必要はなかったのですが・・・

【教訓】質問は具体的な内容付きでしませう！

補足

回答と助言、ありがとうございます。
説明していただく際、大変だったと思います。
申し訳ありませんでした。
教えていただく立場からすると、すごく分かり易かったので助かりました。

質問文の説明不足だった上に、さらに申し上げにくいのですが「右に１・下に１」移動した場合だけでなく、「右に１・下に２」および「右に２・下に１」移動した場合も質問するつもりが、書くのを忘れてしまいました。
本当に申し訳ありません。
式を紙に書いて解読してみたのですが、「右に２・下に１」移動した場合はnの数を増やせばいいとやっと分かりました。
ところが、いくら調べても「右に１・下に２」移動した場合の式が分かりません。

御見苦しいことばかりで申し訳ありませんが、回答していただけたらありがたいです。
どうかよろしくお願いします。

追記：
もしまたエクセル等の質問をするときには、具体的に質問いたします。
助言をありがとうございました。

回答No.2

Cupper

＞今日はギブアップです。
ゆっくり休んでください

別のブックの名称が「Book2」、検索範囲を A1:B10 とした場合で具体的に説明してみます

MATCH関数、これで一致するセルの場所を特定できます
　=MATCH(検索値,範囲 [,照合の型])
と使いますので、A1セルの VLOOKUP関数で求めた値を使って
　=MATCH(A1 , [Book2]Sheet1!B1:B10 , 0)
などとして "検索範囲の上からの行数" を求められます
※ 範囲にはBook名を含めた「絶対参照」意外にも範囲名付けて使ってもOK
　　照合の型を 0 にすると完全一致の値を見つけに行きますが、値が無いとエラーが返ります

あとはOFFSET関数で求められます
　=OFFSET(基準,行数,列数 [,高さ,幅]）
と使いますので、基準を A1セルとした場合
　=OFFSET([Book2]Sheet1!A1 , MATCH(A1,[Book2]Sheet2!B1:B10,0) , 1）
または
　=OFFSET([Book2]Sheet1!A1 , MATCH(A1,Book2!範囲名,0) , 1）
でOK
基準を B1セルにした場合は、行数を 0 にしましょう
※ 高さ と 幅 は省略されるとそれぞれ 1 が指定したことになります
　　範囲を値として返す必要が無い場合は省略可能です
　　範囲を検索する関数に返すと、
　　=SUM(OFFSET(A1,0,0,2,2))　　A1:B2の範囲の合計
　　=COUNTIF(OFFSET(A1,0,2,10,1),">100")　C1:C10の範囲で100より大きい数値の個数
　　などと使うことができます
　　高さ と 幅 はマイナスの数値を当てる事もできます
　　そのときはそれぞれ、基準から上、基準から左を範囲として返してきます
　　工夫次第でいろいろと使えますので、是非覚えておきましょう

どんな関数があるのかを覚えるのは大変でしょうが、
Excelの関数一覧には簡単な説明も併記されているので
全ての関数を一通り見ておくと、後からすぐに見つけられるようになります

お礼

昨日に引き続き、お付き合いくださってどうもありがとうございます。
ねぎらいのお言葉もくださって、とても嬉しかったです。
結論から申し上げると、他の方が提唱してくださったOFFSET関数とINDEX関数の組み合わせの式を使うことに致しました。
しかし、Cupperさんのお陰でMATCH関数という関数がどのようなものか、触れる機会が出来てよかったです。
解説もとても分かり易かったです。
文末での助言も、今後の参考になりました。
これを機に目を通して役立てたいと思います。
日付をまたいでお付き合いいただき、本当にありがとうございました！

回答No.1

Cupper

MATCH関数を組み合わせてみてください

たぶん質問者さんなら、これで解決できると思います

補足

アドバイスありがとうございます。
拝見してからしばらく試行錯誤してみたのですが、MATCH関数の使い方が理解出来ずに、今日はギブアップです。
MATCH関数はVLOOKUPと組み合わせて使うのでしょうか？
MATCH関数を検索してみると、VLOOKUPと組み合わせる方法と、OFFSET関数と組み合わせる方法があり、どちらも試してみたのですが完敗です。
（OFFSETとMATCH関数で出来そうな感じでしたが、OFFSETも今まで使ったことがないため、解決しませんでした）

今日はもう遅いため、作業は明日に持ち越すことにしますので、質問はまだ締め切らずにこのままにしておきます。
もしお時間がありましたら、明日またお願いします。
本当にありがとうございました。