Excel VLOOKUP関数で検索キーより左側の値を取得する方法

2019年6月4日

VLOOKUP関数では

検索対象となる表の検索キーは

最も左端である必要があります。

例えばこの例ではB3セルに

「種類」を指定して

C3セルに対応する「番号」を

取得しようとしていますが、

B6~D8の表では「番号」は

「種類」の左にあるため

取得することができません。

検索キーの左側に取得したい値があるためVLOOKUPで取得できない例

この記事ではその場合の対処法を紹介します。

手順

検索キーを左に列移動

身も蓋もないというか、

それが出来たらそもそも調べてないよ、

と聞こえて来そうですが

とりあえず第一案。

「種類」を左端に移動を検討します。

VLOOKUPが一時的な用途であれば

使い捨ての新規ブックを作り

別シートにコピーしてから

移動させる手もあります。

検索キーの種類を左に列移動した表

移動して問題ない場合は

最も手軽で根本的な解決方法になります。

左側にセル参照だけの列を作成

こちらも若干、身も蓋もないですが、

一番左端に「種類」を

セル参照するだけの

列を作るのも有効です。

左端に種類をセル参照する列を追加

こちらも表の構成を変えてしまいますが、

セル参照だけの単純な数式で済む

メリットがあります。

VLOOKUP関数ではなくINDEX関数とMATCH関数を使用

表の構成を操作したくない場合は

VLOOKUP関数の替わりに

INDEX関数とMATCH関数を使用します。

INDEX(検索対象表の範囲,MATCH(検索値,検索キーの範囲),取得値の列番号)
検索対象表の範囲VLOOKUPの範囲と
同様に指定します。
例ではB6:D8
検索値VLOOKUPの検索値と
同様に指定します。
例ではB3。
検索キーの範囲検索対象表中の
検索キーとなる「種類」の
範囲を指定します。
例ではC6:C8
取得値の列番号VLOOKUPの列番号と
同様に指定します。
例では番号=1,
種類=2,名前=3
INDEX関数とMATCH関数の使用例。セル範囲の色分け表示
INDEX関数とMATCH関数の使用例。実行結果

表の構成を変える必要がなく、

非常にスマートな方法ですが、

INDEX関数とMATCH関数は

VLOOKUP関数よりも知名度が低く、

数式も複雑になるため

作成者以外の人がシートを見たとき

セルの数式の意図が読み辛いという

欠点があることに注意が必要です。

関連記事

使用した機能

指定した範囲の行と列に対応する値を取得(INDEX関数)

検索値に対応するセルの相対的な位置を取得(MATCH関数)

VLOOKUP関数の使い方・活用方法の記事一覧