ExcelのVLOOKUP関数だと右側しか探せない。左側を探すには？

ExcelのVLOOKUP関数は、表の右側にある値を参照することはできますが、左側にある値を参照することはできません。

本記事では、VLOOKUP関数以外の関数を使用して、表の左側にある値を参照する方法を解説します。

VLOOKUP関数でできること

まず、VLOOKUP関数でできることをおさらいしましょう。

動きとしては、第１引数で指定した値を元に、第２引数で指定した表を検索します。そして、ヒットした行の中から、第３引数で指定した列の値を取得する、という動きをしています（第４引数もありますが、ほとんどの場合、FALSEを指定することになると思いますので、ここでは詳しい説明は省きます）。

このように、あらかじめ用意された表の中から、欲しい値をとってくることができるのが、VLOOKUP関数の機能です。
ただ、VLOOKUP関数では、表の一番左にある列で検索し、それより右側にある値を取得することしかできません。

表の左側を参照する方法

では、検索した値よりも左側にある値を取得するには、どうすれば良いのでしょうか。
ここから先で、複数の関数を組み合わせることで表の左側を参照する方法を３つ、紹介します。

XLOOKUP関数を使用する

ひとつめは、XLOOKUP関数を使用する方法です。

XLOOKUP関数の使用方法については「XLOOKUP関数の使い方」にまとめてありますので、そちらを参照してください。

XLOOKUP関数はMicrosoft 365で使用可能な関数です。
利用しているExcelのバージョンでXLOOKUP関数が使えない場合は、以降で紹介する方法を試してみてください。

MATCH関数とOFFSET関数を組み合わせる

ふたつめは、MATCH関数とOFFSET関数を組み合わせる方法です。

まず、どんな関数なのかを簡単に述べると、

• MATCH関数｜検索した値が、指定した範囲内の何番目のセルにあるかを返す関数
• OFFSET関数｜基準となるセルから、指定した行数・列数だけシフトしたセルの参照を返す関数

と、なります。

MATCH関数を使用した簡単な例を挙げると、

第１引数で指定した値を、第２引数で指定した範囲の中から検索します。
上の例では、第１引数で指定したセルE3の「100」という値が、第２引数で指定した列の2番目にありますので、「2」という結果が返っています。

次にOFFSET関数の例を挙げます。

第１引数で指定したセルを基準として、第２引数で指定した分だけ下（マイナス値を指定すれば上）、第３引数で指定した分だけ右（マイナス値を指定すれば左）のセルを参照します。
上の例では、基準として指定したセル「A2」の、「2」行下、「1」行右のセルの値を参照しています。

では、これらを組み合わせて、表の左側を参照する方法を見ていきましょう。

例として、上の例で使った商品表から、単価が100円となっている商品を探し、その名称を取得する方法を取り上げます。

ポイントは、

• OFFSET関数の第１引数に、取得したい値のある列の上のセルを指定すること。
• OFFSET関数の第２引数に、MATCH関数を指定すること。

の２点です。

OFFSET関数の第１引数に取得したい値のある列の上のセルを指定することで、そこを基準に表を参照する準備ができます。
言い換えると、第１引数にセル「B2」を指定することで、第２引数に「1」を入れると１行下の「ペン」を取得でき、「2」を入れると２行下にある「消しゴム」を取得できるようになるということです。

ここで、第２引数に「1」や「2」などの固定値を入れるのではなく、MATCH関数で算出した「検索対象の値が、指定した範囲の何番目のセルにあるか」を入力することで、検索した値と同じ行にある値を取得できるようになるのです。

MATCH関数とINDEX関数を組み合わせる

次に、MATCH関数とINDEX関数を組み合わせる方法を見ていきましょう。

それぞれの関数を簡単に説明すると、

• MATCH関数｜検索した値が、指定した範囲内の何番目のセルにあるかを返す関数
• INDEX関数｜あるセル範囲の、指定した行（列）にあるセルを返す関数
  ※INDEX関数には他にも機能がありますが、今回の目的のためには上記の理解で十分です。

となります。

INDEX関数の簡単な例を挙げると、

第１引数でセル範囲「C3:C5」を指定しています。第２引数で、そのうちの２番目を取得することを指定しており、結果、セル「C4」にある値「100」が返されています。

では、MATCH関数とINDEX関数と組み合わせて表の左側を参照する方法を見ていきましょう。

ポイントは、

• INDEX関数の第１引数に、取得したい値のある列を指定すること。
• INDEX関数の第２引数に、MATCH関数を指定すること。

の２点です。

INDEX関数の第１引数に、取得したい列を指定しています。第２引数に「１」を入れるとセルB3の「ペン」が取得され、「２」を入れるとセルB4の「消しゴム」が取得されます。
MATCH関数＋OFFSET関数のときと同様、第２引数にMATCH関数の算出結果を入れることで、MATCH関数で検索した値と同じ行の値を取得することができるのです。

まとめ

VLOOKUP関数の代わりに、表の左側にある値を参照する方法を２通り紹介しました。

• MATCH関数＋OFFSET関数を使う方法
• MATCH関数＋INDEX関数使う方法
  

今回の記事で取り上げた関数は、どれも応用の幅が広く、使い方をマスターすれば色々な局面で使えると思います。

ぜひ、活用してみてください。