Excel解説記事｜Excelセミナー・効率化支援のすごい改善

Excelセミナー：VLOOKUP関数で検索値より左側の値を取得できるか？

まず、エクセル作業における最重要必須関数である、VLOOKUP関数の使い方は必ず慣れておいて下さい。これが使いこなせなければまずお話になりません。

【関数名】
VLOOKUP関数

【書式】
=VLOOKUP(検索値,検索範囲,列数,0)

【機能】

検索範囲の一番左の列から検索値と同値のセルを探し、そのセルから第三引数で指定した列数目にあるセルの値を返す。

検索範囲の一番左の列から指定した列数目の値を返す、ということですから、その列より右側にある列の値を返す、ということになります。

ここで、素朴な疑問がわきます。

検索した列より左側の値を取得することはできないか？

例えば、第三引数をマイナスにすればできるのではないか…。

私も試してみたことがありますが、ダメでした。

結論、VLOOKUP関数ではこれはできない、というように私は考えています。

もし、違うよ、というご意見の方いらっしゃいましたらご教授頂けましたら幸いです。

では、検索した列より左側の値を引っ張るにはどうしたらいいのか。

私はこういうときには、OFFSET関数とMATCH関数を組み合わせることで解決します。

では実際のデータで実行してみます。

1．こちらのデータをコピーして、エクセルシートのA1セルに貼って下さい。

使用品種,NO,担当者名,,NO,担当者名,使用品種
ゴムゴムの実,1,モンキー・D・ルフィ
グラグラの実,2,エドワード・ニューゲート
ニキュニキュの実,3,バーソロミュー・くま
ヒトヒトの実,4,トニー・トニー・チョッパー
トリトリの実,5,ペル

2.そのまま、「データ」タブから「データ区切り」をクリックします。

3．「元のデータの形式」が「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」が選択されていることを確認し、「次へ」をクリック

4．「区切り文字」で「カンマ」にチェックを入れ、「完了」をクリック

これで、上記のデータがエクセルで使えるようになったと思います。

データをエクセルに取り込めたら、次のような状態になっていることを確認してください。

A列→担当者名
B列→No
C列→使用品種
E1→NO
F1→担当者名
G1→使用品種

ここで、E2セルに入力した数字に応じて、F2、G2セルにそれぞれ担当者名、使用品種が入力される仕組みを作っていきます。

まずF2セルは問題ないですね。普通にVLOOKUP関数で対応できます。

=VLOOKUP(E2,B:C,2,0)

ところが、G2セルの「使用品種」は、検索列であるB列よりも左側にあるため、VLOOKUP関数では対応できません。

ここで、MATCH関数とOFFSET関数を組み合わせます。

G2セルに入れるのは次の数式です。

=OFFSET(B1,MATCH(E2,B2:B6,0),-1)

OFFSET関数では第二引数の移動行数、第三引数の移動列数にマイナスの数字を指定することができます。つまり、基準セルよりも上、左のセルを参照することができます。

この性質を利用して、VLOOKUP関数ではできなかった検索列より左側のセルも参照することができるようになります。

OFFSET関数とMATCH関数の性質をきちんと理解した上で、今回ご紹介した数式をじっくりと解読してみて頂きたいと思います。

独学でやってきた方、体系的に学びたい方、もっと効率的に使いたい方へ…
過去10年間毎週開催・400回以上の開催で3700名以上が参加したExcelセミナーです。
実務直結・超実践型・一日完結・生涯無期限アフターサポートがついてます。

Excel解説記事