キャンペーンの対応表のA列がコード、B列がキャンペーン名で、コードと名前の間に「開始日」の列を1本足したとします。リード一覧のタブには、キャンペーン名を引いているこの数式が入っています。
=VLOOKUP(D2, 'キャンペーン対応表'!$A:$C, 2, FALSE)
列を足したあと、この式が返すのはキャンペーン名ではなく開始日です。エラーは出ない。
本業で、項目が29列くらいある管理シートを使っています。データの更新や項目の追加があるたびに数式ごと組み直していて、週に1回か月に1回、1回で30分から1時間かかります。列番号を数字で書いている限り、返す列より左に列を足すたびにこの組み直しです。見出しの文字から列番号を探すか、XLOOKUPで列そのものを指す形にすれば、列を足しても数式は直さずに済みます。
列を足しても、数式の2は2のまま
上の式の2は、範囲の左から2列目という意味の数字です。ここには数字しか書けず、列の名前は書けません。
対応表のA列とB列の間に列を挿入すると、範囲の$A:$Cは$A:$Dに広がり、$A$1:$C$100のように行番号付きで書いていても$A$1:$D$100になります。範囲は壊れない。
数式の2は2のままです。範囲の2列目は、今足した開始日の列です。VLOOKUPはそこを返す。値が入れ替わっているだけなので、集計まで進んでから気づきます。
列を足すたびに、対応表を引いている数式の数字を全部数え直します。29列のシートでこれをやるのが、さっきの30分から1時間です。
見出しの文字から列番号を出す
列番号の所に、数字の代わりにMATCHを入れます。
=VLOOKUP(D2, 'キャンペーン対応表'!$A:$Z, MATCH("キャンペーン名", 'キャンペーン対応表'!$1:$1, 0), FALSE)
MATCHは、探した値が範囲の何番目にあるかを返す関数です。「キャンペーン名」が対応表の1行目の2番目にあれば2、開始日を間に足して3番目に動けば3を返します。末尾の0は完全一致で、VLOOKUPのFALSEと同じです。
この形にしておくと、対応表に同じ列を足しても、数式はキャンペーン名を返し続けます。MATCHが出す数字が2から3に変わるだけで、数式は触らない。
気をつける所は、MATCHで探す見出しの行とVLOOKUPの範囲を、同じ列から始めることです。VLOOKUPの番号は範囲の左端から数えるので、範囲が$B:$Zなら見出しの行も$B$1:$Z$1にしないと、1列ずれた値が返ってきます。見出しの行だけ範囲より右まで広げていると、範囲の外にある見出しを拾ったときに#REF!です。同じ名前の見出しが2つあるときは、MATCHは先にある方の位置を返します。
ただ、この形は見出しの文字に頼る。誰かが見出しを「CP名」に変えると、MATCHが見つけられずに#N/Aです。それでも、違う値が返ってくるより#N/Aで止まる方が気づけるので、私はこちらの壊れ方の方がましだと思っています。
XLOOKUPなら、列そのものを指す
=XLOOKUP(D2, 'キャンペーン対応表'!$A:$A, 'キャンペーン対応表'!$B:$B, "対応表に無い")
探す列と返す列を、別々に書きます。列番号がどこにもない。
対応表に同じ列を足すと、結果の範囲の$B:$Bは$C:$Cに書き換わり、数式はキャンペーン名を返し続けます。VLOOKUPの範囲が$A:$Dに広がったのと同じ動きが、今度は返す列そのものに効いています。見出しの文字も使っていないので、見出しを変えられても大丈夫です。
新しく書くなら、この形がいいと思います。動いているVLOOKUPまで急いで替える必要はありません。
動いている数式は、列を足すときに替える
一度に全部書き換えなくても、列を足すときに、足した列より右の列を返している数式だけ替えれば足ります。どの数式を替えるかを決めるには、それぞれの数式が対応表の何列目を返しているかを見ることになる。私が作ったSheetPeekというChrome拡張を入れておくと、数式のセルを選んだまま、対応表の見出しの行と先頭の数行が画面の横に出ます。何列目に何があるかは、その場で数えられる。
数式の動きは、次の公式ヘルプで確かめました(2026-10-06)。
- VLOOKUP: https://support.google.com/docs/answer/3093318?hl=ja
- MATCH: https://support.google.com/docs/answer/3093378?hl=ja
- XLOOKUP: https://support.google.com/docs/answer/12405947?hl=ja
SheetPeekはChromeウェブストアで公開しています。入れるとGoogleスプレッドシートの画面にパネルが出て、数式の入ったセルを選ぶだけで動きます。