VLOOKUP関数の第3引数を数値で指定していると、表の途中に列を挿入・削除した際に、取得する列がずれて結果が変わる場合があります。
この問題を避ける方法として、XLOOKUP関数に置き換える方法や、INDEX関数とMATCH関数を組み合わせる方法があります。
この記事では、VLOOKUP関数を残したまま、第3引数の列番号をMATCH関数またはCOLUMN関数で求め、表の途中にある列の挿入・削除に対応する方法を紹介します。
なお、今回の方法で対応できるのは、主に検索列と取得列の間にある列の挿入・削除です。検索列または取得列そのものを削除した場合は、表や数式を修正する必要があります。
MATCH関数を使用する方法
MATCH関数を使用する方法では、見出し名を基準に取得する列の位置を求めます。数式を後から見直したときに、どの列を取得しているのか確認しやすい方法です。
メリット・デメリット
VLOOKUP関数とMATCH関数を組み合わせた場合のメリットとデメリットです。
メリット
- 検索・取得範囲の開始列と取得する列の間にある列を削除しても、取得する列がずれない
- 検索・取得範囲の開始列と取得する列の間に列を挿入しても、取得する列がずれない
- COLUMN関数と比べると、どの列を取得しているのか確認しやすく、後から数式を変更しやすい
デメリット
- 同じ見出しが複数ある場合、MATCH関数は左側にある同名の見出しの位置を返すため、意図しない列を取得する場合がある
- 取得する列を削除すると、参照先がなくなり、「#REF!」のエラーが発生するため、修正する必要がある
- VLOOKUP関数だけを使用する場合より数式が複雑になり、仕組みを理解していないと後から修正しにくい
数式
数式は次のようになります。
=VLOOKUP(検索値,検索・取得する範囲,MATCH(取得する列の見出し,検索・取得する範囲の見出し,0),FALSE)
サンプル
下の表で、顧客コード[AB-001]の電話番号を取得する場合、次の数式になります。
=VLOOKUP("AB-001",C3:F6,MATCH(F2,C2:F2,0),FALSE)この数式であれば、C列とF列の間に列を挿入・削除しても電話番号を取得します。
「MATCH(F2,C2:F2,0)」では、F2の見出し「電話番号」が、C2:F2の左から何番目にあるかを調べます。
「電話番号」は4番目にあるため、MATCH関数は「4」を返します。その結果、VLOOKUP関数は検索・取得範囲の4列目から電話番号を取得します。
表の途中に列を挿入・削除すると、参照範囲と見出しの位置も自動的に調整されるため、取得する列番号を求め直せます。
COLUMN関数を使用する方法
COLUMN関数を使用する方法では、検索列と取得列の列番号の差から、VLOOKUP関数の第3引数を求めます。
メリット・デメリット
VLOOKUP関数とCOLUMN関数を組み合わせた場合のメリットとデメリットです。
メリット
- 検索・取得範囲の開始列と取得する列の間にある列を削除しても、取得する列がずれない
- 検索・取得範囲の開始列と取得する列の間に列を挿入しても、取得する列がずれない
デメリット
- 検索・取得範囲の開始列を削除すると、参照先がなくなり、「#REF!」のエラーが発生するため修正する必要がある
- 取得する列を削除すると、参照先がなくなり、「#REF!」のエラーが発生するため、修正する必要がある
- VLOOKUP関数だけを使用する場合より数式が複雑になり、仕組みを理解していないと後から修正しにくい
数式
数式は次のようになります。
=VLOOKUP(検索値,検索・取得する範囲,COLUMN(取得する列)-COLUMN(検索・取得範囲の開始列)+1,FALSE)
サンプル
下の表で、顧客コード[AB-001]の電話番号を取得する場合、次の数式になります。
=VLOOKUP("AB-001",C3:F6,COLUMN(F:F)-COLUMN(C:C)+1,FALSE)検索・取得範囲の開始列がC列（3列目）、取得する列がF列（6列目）にあります。「6-3+1=4」となり、検索・取得範囲の4列目にあるデータを取得します。
VLOOKUP関数の検索・取得範囲は、第2引数で指定した「C3:F6」です。C列を基準に4列目はF列となるため、電話番号を取得します。
まとめ
VLOOKUP関数の第3引数を数値で指定していると、表の途中に列を挿入・削除した際に、取得する列がずれる場合があります。
MATCH関数やCOLUMN関数で列番号を求めれば、INDEX関数に置き換えず、VLOOKUP関数を残したまま列の挿入・削除に対応できます。
特にMATCH関数を使用する方法は、取得する列を見出し名で指定するため、どの列を取得しているのか確認しやすい点がメリットです。
ただし、検索列や取得列そのものを削除した場合は、表や数式を修正する必要があります。表の構成を変更した後は、意図した結果が表示されているか確認してください。
