ExcelのVLOOKUP関数の列番号をマイナス方向にする方法

この記事では、ExcelのVLOOKUP関数の列番号をマイナス方向にする方法をご紹介します。

データ検索をする際、検索値を探す列が必ず左端にあるとは限りません。データを並び替えたくない場合に検索値の前の列を検索する方法があります。

以下では、VLOOKUP関数のほかにINDEX関数やOFFSET関数を使って列番号をマイナス方向に検索する方法をご紹介します。

VLOOKUP関数で列番号をマイナスに検索することはできる？

VLOOKUP関数で列番号をマイナスに検索するとは、上記の画像のように検索値の列（例：C列）よりも前の列（例：A列）の値を抽出するということです。

VLOOKUP関数はデータ検索に使われる便利な関数ですが、検索値を探す範囲を必ず左端にするという制約があります。

基本的には検索値より前の列を検索することはできませんが、「配列」を使ってマイナス方向に検索する方法があります。

ただし、配列式の修正は手間がかかるためおすすめしません。

同記事内に、INDEX関数やOFFSET関数を使って前の列を検索する方法をご紹介しているので、なるべくVLOOKUP関数を使わないようにしましょう。

VLOOKUP関数を使って列番号をマイナス方向にする方法は計算に時間がかかったり数式を変更するのに手間がかかったりするため、INDEX関数やOFFSET関数を使う方法をおすすめします。

上記の画像のような表を用意します。

E3セルに検索値（例：スプーン）を入力します。

E6セルに「=VLOOKUP(E3, CHOOSE({1,2}, C3:C10, A3:A10), 2, FALSE)」を入力します。

配列式なのでCtrl+Shift+Enterを押します。

上記の数式は、E3セルの値に完全一致する値をC3セルからC10セルの間で検索して、該当する行に紐づく値をA3からA10の範囲で探して値を返します。

CHOOSE関数は、リストから指定した位置の値を取り出すことができます。書式は「=CHOOSE(インデックス,値1,[値2]…)」

CHOOSE関数の詳細は、以下の記事でご紹介しています。

E6セルに結果が表示されました。

VLOOKUP関数とCHOOSE関数を組み合わせて、列番号をマイナス方向に検索して、検索値より前の列を抽出することができました。

上記の画像のような表を用意します。

E3セルに検索値（例：スプーン）を入力します。

E6セルに「=INDEX(A3:A10,MATCH(E3,C3:C10,0),1)」を入力して、Enterを押します。

上記の数式は、MATCH関数でE3セルの値に完全一致する値をC3セルからC10セルの間で検索して何行目にあるのかを求めます。

MATCH関数で取得した行番号を元にINDEX関数で列番号を取得します。今回は、選択している列がA列しかないので引数の列番号は1になります。

INDEX関数は、指定した行数と列数が交差する位置にあるセルやセル参照を求めます。今回使う書式は「=INDEX(配列,行番号,[列番号])」です。

MATCH関数は、特定の条件に当てはまる指定した値が範囲の何番目にあるのかを求めます。書式は「=MATCH(検査値,検査範囲,[照合の型])」です。

INDEX関数とMATCH関数の詳細や、2つの関数を使った別の数式を以下の記事でご紹介しています。

E6セルに結果が表示されました。

INDEX関数とMATCH関数を組み合わせて、列番号をマイナス方向に検索して、検索値より前の列を抽出することができました。

上記の画像のような表を用意します。

E3セルに検索値（例：スプーン）を入力します。

E6セルに「=OFFSET(C3,MATCH(E3,C3:C10,0)-1,-2,1,1)」を入力して、Enterを押します。

上記の数式は、MATCH関数でE3セルの値に完全一致する値をC3セルからC10セルの間で検索して何行目にあるのかを求めます。

この際、必ずMATCH関数の後ろに「-1」を入力します。MATCH関数は選択したセルを「1」から数えますが、OFFSET関数は選択したセルを「0」から数えるため、行がずれないように調整しています。

MATCH関数で取得した行番号を元にOFFSET関数で列番号を取得します。

OFFSET関数は、指定した位置の値や指定した行数と列数をずらしたセル範囲を返すことができます。書式は「=OFFSET(基準,行数,列数,[高さ],[幅])」です。

OFFSET関数の詳細は以下の記事でご紹介しています。

E6セルに結果が表示されました。

OFFSET関数とMATCH関数を組み合わせて、列番号をマイナス方向に検索して、検索値より前の列を抽出することができました。