そこで、検索場所の列の左側に列を挿入してしまうと本来検索する列が右側にズレるため間違った列を検索してしまうことになってしまいます。
そこで、そのズレを防止するために、検索する列番号を“MATCH関数”を使って求めてやります。
“COLUMN関数”でも同じような効果があるのですが、“MATCH関数”の方がより間違いなく検索できます。
こんな様な3列の表があります。その右側でVLOOKUP関数で得意先コードから都道府県を検索しています。
①は都道府県の列番号を“3”としたVLOOKUP関数です。
②が“COLUMN関数”で見出しの“都道府県”のセル“D3”の列番号を求めてそれを列番号にしています。ただここではD3の列番号は“4”になるため“-1”して“3”にしています。
③が“MATCH関数”で表の見出しの範囲（B3:D3）から“都道府県”の列番号を求めています。
いずれも、得意先コード“2”に対し“福岡県”が正しく検索されています。
では、意地悪テストです。
得意先コードの右側に列を挿入します。
すると都道府県の列は右にズレて表の中で4列目に変わります。
すると①の検索列を“3”と指定したVLOOKUP関数では“小料理なんごく”が表示されてしまいました。
①では列の挿入によりVLOOKUP関数の検索範囲は$B$3:$E$23に拡大されるため都道府県の検索列は左から“4”番目にならなければいけません。
②の“COLUMN関数”では列の挿入により検索セルも相対的に右にズレて“E3”を検索するようになったため、“E3”の列番号“5”“-1”で4になって都道府県が表示されています。
③の“MATCH関数”は列の挿入により検索範囲がB3:E3に拡大し“都道府県”と言う文字を検索し“4”を返しVLOOKUP関数は都道府県から検索しています。
次は、表の中ではなく外側（右側）に列が送入された場合です。
ここで、都道府県が正しく表示されないのは②の“COLUMN関数”で指定した場合です。
①では“VLOOUP”の検索範囲が表の右側の列の挿入により“$C$3:$E$23”に右に移動するため“都道府県”の列は表の左から3番目であることに変わりはありません。ので正しく“福岡県”と表示されました。
では問題の“COLUMN関数”を使用した②の場合です。
列の挿入により検索するセルは“E3”になり、右にズレました。ので、“5”“-1”で“4”になり、VLOOKUP関数の検索範囲の外を指定してしまっています。
“MATCH関数”を使用した③の場合は列の挿入により検索範囲が“C3:E3”と右に移動しているだけなので“都道府県”の検索結果は“3”になり正しく“福岡県”が表示されました。
と言うことで、VLOOKUP関数を使って検索したいのは絶対的な列番号ではなく表の中の“都道府県”の列番号であるわけなので、表の中の“都道府県”の列番号を返してくれる“MATCH関数”を使うのが妥当なのは当たり前と言うことになる。。。
関連ページ
・VLOOKUPで列方向の検索をして表示させてみた
・COLUMN（COLUMNS）関数で列番号を求めてみた～
