Aさん VLOOKUP関数で検索を行なう表の右端をキーに検索をかけることはできないのでしょうか？
森田 結論から言うとできません！
私も思いついて試してみたことありますけど、見事にできませんでした(笑)
VLOOKUP関数は必ず検索したい表の1番「左」側の列をキーに検索をかけますので、その仕様に合わせて、こちら側で表を加工してあげる必要があります。
今回は、その具体的な対応策について解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- データの転記・集約作業が多い人
- エクセルを使う頻度が高い人
- 事務職の人
ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方
VLOOKUP関数で引数「範囲」の1番右の列を検索できないのか？
つまり、以下のようなイメージですね。
上記はシンプルな2列の表で、VLOOKUP関数での取り扱いは各列以下のとおりです。
- 左側の列（1列目）はVLOOKUP関数の戻り値（返り値）にしたいデータ群
- 右側の列（2列目）は引数「検索値」にしたいキーワードがあるデータ群
通常は左から右方向にVLOOKUP関数は検索してくれますが、今回の表はいつもと逆で、右から左方向へ検索したいということになります。
私はかつてVLOOKUP関数の引数「列番号」をマイナスの値にすれば「右から左方向へ検索することができるのでは」と、素人考えで試しましたが、案の定エラーになりました。
（冷静に考えるとエラーになって当然な数式ですね）
つまり、VLOOKUP関数の仕様上、引数「検索値」を探す対象の列は、必ず検索したい表の1番「左」側になければならないということですね。
引数「検索値」がある列を検索したい表の1番「左」に持ってこよう！
今回の状況でVLOOKUP関数を使うためには、VLOOKUP関数の方ではなく、検索したい表に対して工夫すれば良いです。
つまり、検索したい表の1番左側の列に引数「検索値」を探す対象の列を持ってくればOKです。
作業セル（列）へリンク貼り付けがおすすめ
一番おすすめなのは、次のように、引数「検索値」を探す対象の列を表の1番左側の余白の列へリンク貼り付けすることです。
ここで、そもそも余白の列がない場合は列を挿入して作業用の列を設ければOKです。
あとは、通常通りにVLOOKUP関数でリンク貼り付けした列が1番左の列になるようにセットすれば良いです。
ちなみに、リンク貼り付けとは、貼り付けの種類のひとつで、コピー元のセルの値を参照させる貼り付け方法です。
たとえば、上記の例であれば、A2セルは「=C2」のように、コピー元のC2セルを参照する数式が自動的に入ります。
後々、引数「検索値」を探す対象の列のデータへ変更や修正が入ったとしても問題ないようにしているわけですね。
ちなみに、こういった変更・修正の可能性がないのであれば、引数「検索値」を探す対象の列を切り取り、表の1番左の列へ「切り取った列の挿入」でも良いですよ。
状況に合わせて最適な方法を選択してくださいね。
リンク貼り付けの手順
なお、リンク貼り付けの手順は以下のとおりです。
- 「検索値」を探す対象の列のデータをコピー
- 表の1番左の列の作業セルを選択
- リボン「ホーム」タブを選択
- 「貼り付け」をクリック
- 「リンク貼り付け」をクリック
「リンク貼り付け」の詳細について知りたい方は、こちらの記事もご参考になさってください。→こちら
サンプルファイルで練習しよう！
では、実際に今回のテクニックを使ってみましょう！
サンプルの条件
今回の題材は次のとおりです。
「商品」というシートに単価・くだものの名称が一覧表になっています。
この2列が通常とは逆なので、このままではVLOOKUP関数で検索できないですね。
この「単価」を別シート「台帳」のC列の2行目以降に転記したいとします。
この「台帳」シートのB列には「くだもの」の名称がすでに入っているので、これをVLOOKUP関数のキーワードにしていきましょう。
実際に操作しよう！
可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。
サンプルファイル_VLOOKUP関数_右側検索対策
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ファイルを開いたら次の手順を実施してください。
- 「商品」シートを選択
- C2～C6セルをコピー
- A2～A6セルへリンク貼り付け
- 「台帳」シートを選択
- C2セルへ「=VLOOKUP($B2,商品!$A:$B,2,0)」を入力
※赤字の部分をコピーして貼り付けてください。 - C2セルをコピー
- C3～C6セルへ貼り付け（ペースト）
上記手順を行った結果は「商品 (加工済み)」シート・「台帳 (加工済み)」シートにありますので、このシートの内容と実際に操作した結果が同じなっていればOKです。
【補足】表に作業セルを追加したくないならVLOOKUP関数以外の検索/行列関数を使う！
検索したい表の左側に余白がなく、マクロなどの問題で列を追加できないなど、検索したい表を加工することが困難な場合もあるでしょう。
そんな場合は、VLOOKUP関数では不可能ですが、別の関数であれば検索が可能ですよ。
次の2通りの関数の組み合わせが使い勝手が良いです。
INDEX関数の方が使いやすく、利用している方は多いイメージですね。
OFFSET関数＋MATCH関数
この記事をご参照ください。
OFFSET関数とMATCH関数を組み合わせてVLOOKUP関数より柔軟に表から検索値を返す方法 | Excelを制する者は人生を制す ～No Excel No Life～
INDEX関数＋MATCH関数
※記事を別途更新予定です。
さいごに
今回は、改めてVLOOKUP関数の仕様による「制約」と向き合う内容でした。
便利なVLOOKUP関数にも弱点はあるということですね。
このように、関数の仕様によって、表のデータの方を工夫しなければならないことは往々にしてあります。
ケースごとに関数と表のどちら（あるいは両方）を工夫すれば良いかは、実務の中で試行錯誤の経験を積み重ねていくと、応用力がついてきますよ。
その他のVLOOKUP関数のテクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 今回はVLOOKUP関数の弱点部分についてスポットを当てる内容になりましたが、こういう制約があることも知っておいた方が、よりVLOOKUP関数を使いこなせるようになりますよ。
VLOOKUP関数が苦手な部分は、表へ一手間加えてあげるか、別のより適した関数を覚えれば良いのですから。
各関数や機能も、人間といっしょで強みと弱みを両方認識しておいた方が良いことは共通だと思います。
