ExcelのVLOOKUP関数とOFFSET関数を組み合わせる方法

この記事では、ExcelのVLOOKUP関数とOFFSET関数を組み合わせる方法をご紹介します。

VLOOKUP関数とOFFSET関数を組み合わせる利点は、データ数に応じてVLOOKUP関数の範囲を指定できることです。データを追加した際に、範囲を修正する必要がないのでミスを減らすことができます。

以下では、OFFSET関数を組み合わせた便利な使い方をご紹介します。

以下では、VLOOKUP関数とOFFSET関数を組み合わせた数式をご紹介します。

VLOOKUP関数で範囲をセル参照している場合、データを追加した際に範囲を修正するのを忘れてしまうと正しい結果を取得できません。

また、データの追加に対応できるように列全体を指定すると、データが重くなってしまうためおすすめできません。

以下では、VLOOKUP関数とOFFSET関数を組み合わせて、データが追加された場合は、自動で範囲に追加されるようにする方法をご紹介します。

上記のようなデータを用意して、G2セルとG3セルに検索値を入力します。

例では、検索値（例：No.）に紐づく担当者をH列に抽出して、5行目以降にデータが追加された場合に、数式を修正しなくてもデータ検索ができるようにしていきます。

H2セルに「=VLOOKUP($G2,OFFSET($A$2,0,0,COUNTA($A:$A)-1,5),3,FALSE)」を入力して、Enterを押します。

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

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

COUNTA関数は空白ではない数値や文字列が入力されたセル数を数えます。書式は「=COUNTA(値1,[値2],...)」です。

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

H2セルに入力した数式は、A2セルを基準にして行数をCOUNTA関数で自動で取得して、列数を指定するとVLOOKUP関数の範囲が自動で可変するように指定できます。

以下で、詳しく解説します。

=OFFSET(基準,行数,列数,[高さ],[幅])」の基準でA2セルを指定して基準から行数0、列数0に移動したセル、つまりA2セルが範囲の始まりのセルになります。

[高さ]は、COUNTA関数で自動で取得します。A列全体を指定しているので、A1セルから数値や文字列が入力されたセルまでを取得しますが、1行目は見出し行なので、「-1」をすることで範囲を正しく指定できます。

[幅]は、データ範囲に応じて指定します。例では項目が5列目まであるので、幅（列数）は5を指定しています。

H2セルに検索値（例：RJO1）に紐づく「担当者」山本が表示されました。

H2セルのフィルハンドルをドラッグして数式をコピーします。

H2セルとH3セルに結果を表示することができました。

以下では、6行目にデータを追加した場合に、数式を修正しなくても自動で範囲が変更されているかを確かめていきます。

6行目にデータを追加します。

検索値に追加したデータの「No.」RJ05を入力すると、検索値に紐づく「担当者」石井が表示されました。

VLOOKUP関数の範囲にOFFSET関数とCOUNTA関数を組み合わせることで、数式を修正しなくても範囲が自動で移動します。

また、列全体を指定する必要がないので計算速度も速くなります。

VLOOKUP関数は、検索列を必ず左端にしなければならないという弱点があります。

検索列より左側の列を検索することはできないので、不便に感じることがありますが、MATCH関数とOFFSET関数を組み合わせることで検索列より左側を簡単に検索することができます。

詳しい方法は、以下の記事「OFFSET関数とMATCH関数で列番号をマイナス方向にする方法」セクションでご紹介しています。