Excel VLOOKUP関数で左側の値を取得する方法

2020年11月23日

VLOOKUP関数では検索対象となる列が最も左端である必要があります。

検索列(種類)の左側に取得したい値(番号)があるためVLOOKUPで取得できない例の図解

例えばこの例ではB3セルに「種類」を指定して

C3セルに対応する「番号」を取得したいですが、

B6~D8の表では「番号」は「種類」の左にあるため

普通にVLOOKUP関数を使うと不可能です。

世の中の台帳は基本的に左端に番号やコードがありその右に名前や名称があります。

そして名前や名称から、番号やコードを取得したいケースがあるため

様々な形で検索値の左側の値を取得したいケースが発生します。

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

左側の値を取得する手順

まず方法は4つあります。

  1. XLOOKUP関数を使用
  2. 検索列を左に移動
  3. 左側にセル参照だけの列を作成
  4. INDEX関数とMATCH関数を使用

方法1は上位互換のXLOOKUP関数を使用します。

方法2と3は表の構成を変更する手段です。

方法4はVLOOKUP関数ではなくINDEX関数とMATCH関数を使用します。

XLOOKUP関数を使用

なお2020年1月にXLOOKUP関数という非常に便利な上位版が追加されました。

こちらでは指定の方法が変わり、左端に検索範囲がなくても利用できます。

XLOOKUP関数で解決する例

検索列を左に移動

身も蓋もないですが一案。

検索対象列を左端に移動を検討します。

もし一時的な用途であれば使い捨てのブックを作り

別シートにコピーしてから移動させるのも有効です。

検索列の種類を左に列移動した状態の図解

表の構成を変更して問題ない場合は、最も手軽で根本的な解決方法になります。

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

こちらも少し身も蓋もないですが、

一番左端に検索対象列をセル参照するだけの列を作るのも有効です。

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

こちらも表の構成を変えてしまいますが、セル参照だけの単純な数式で済みます。

また左端の列を他に使わない場合、列の非表示で見えなくすれば

見た目だけは変更のない状態にできます。

INDEX関数とMATCH関数を使用

表の構成を操作したくない場合はVLOOKUP関数の替わりに

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

=INDEX(表示項目の範囲,MATCH(検索値,検索範囲,0))
INDEX関数とMATCH関数の使用例と引数の図解

表の構成を変える必要がなく非常にスマートな方法ですが、

INDEX関数MATCH関数VLOOKUP関数よりも

知名度が低く数式も複雑になるため作成者以外の人がシートを見たとき

セルの数式の意図が読み辛いという欠点があることに注意が必要です。

関連記事

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