VLOOKUPの第4引数を省略すると、静かに間違える。既定値がTRUEだから

VLOOKUPを書く。最後の引数を、つい省略する。

=VLOOKUP(A2,商品表,3)

エラーは出ない。それらしい値が返ってくる。そしてその値が間違っていることがある。

原因は、省略したときの既定値にある。省略するとTRUE（近似一致）になる。 完全一致で探しているつもりが、近い値を拾ってきている。

これがExcelでいちばん静かな事故だと思う。

この記事は育ちます。公開後も、会話パート・トーク・補足を足していきます。
公開 2026-10-09 ／ 最終更新 2026-10-09（公開）

水深 — 4つめの引数が何をしているか

VLOOKUPの形をおさらいする。

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

4つめが検索方法だ。

• FALSE（または0）　完全一致。ぴったり同じものだけを探す
• TRUE（または1）　近似一致。検索値以下で最大のものを探す
• 省略　TRUEと同じ扱いになる

「近似」という言葉が誤解を招く。似ているものを探すのではない。検索値を超えない範囲で、いちばん大きい値を拾ってくる。

そして近似一致には、範囲が昇順に並んでいることという前提がある。並んでいないと、結果は保証されない。

何が起きるか、具体例で

商品コードから単価を引く表を考える。

ここで =VLOOKUP(“A105”,範囲,3) と書いたとする。A105は表にない。

• FALSEを指定していれば　→ エラー（該当なし）が返る
• 省略した場合　→ 250（A103の単価）が返る

A105の単価として、ボルトの250円が返ってくる。エラーは出ない。

見つからなかったことに気づけない。これが怖いところだ。

エラーは「ない」と教えてくれる。近似一致は、「ない」を「近いもの」にすり替える。

並び順が違うと、もっと壊れる

昇順に並んでいない表で近似一致を使うと、結果は予測できない。

VLOOKUPの近似一致は、内部で二分探索をしている。範囲の真ん中を見て、大きいか小さいかで半分に絞る。これを繰り返す。

高速だが、並んでいることが前提だ。並んでいなければ、途中で間違った方向に進む。そして止まる。

だから、並んでいない表で近似一致を使うと、毎回違う間違い方をする。行を1つ足しただけで答えが変わることもある。

原因を追いにくい不具合の、典型的な形だと思う。

近似一致が役に立つ場面もある

では近似一致はいらないのかというと、そうでもない。階段状の判定には向いている。

送料の表を考える。

ここで =VLOOKUP(3000,範囲,2,TRUE) と書くと、800が返る。3000を超えない最大の区分（1000以上）を拾うからだ。

IF文を重ねる必要がない。

こういう使い方をするなら、TRUEを明示的に書く。省略しない。「意図してTRUEにした」のか「書き忘れた」のかが、あとで読む人に分かるようにしておく。

用途はこう分かれる。

• コードや名前から引く　→ FALSE
• 金額や重さの区分から引く　→ TRUE（表は昇順に並べる）

FALSEを書くのを忘れないために

対策は単純で、必ず4つめを書く。それだけ。

書き癖として、こうしておくといい。

=VLOOKUP(A2,範囲,3,FALSE)

0でもいい。=VLOOKUP(A2,範囲,3,0) も同じ意味で、打つのが速い。

社内でテンプレートを配るなら、FALSEまで入れた状態で配る。書き足すより、消すほうが意識的な行為になる。

そもそもVLOOKUPをやめるという手

ここから先は、もう少し根本的な話になる。

VLOOKUPには、4つめの引数以外にも弱点がある。

① 列番号が数字なので、列を挿入すると壊れる

「3列目」と書いてあるところに列を1本挿入すると、3列目の中身が変わる。式は壊れないが、返ってくる値が変わる。これもエラーが出ない。

② 左側を探せない

検索値は、範囲のいちばん左の列になければならない。コードが右側にある表では使えない。列を並べ替えるか、作業列を足すことになる。

③ 範囲が広いと重い

検索のたびに範囲を走査する。列を丸ごと指定していると、それが件数ぶん繰り返される。

XLOOKUPなら、全部片づく

新しいほうの関数を使えば、3つとも解決する。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合)

• 既定が完全一致。省略しても近似一致にならない
• 検索する列と返す列を別々に指定するので、左側も探せる。列の挿入にも強い
• 見つからない場合の値を、4つめでその場に書ける。IFERRORで包まなくていい

書き比べてみる。

VLOOKUP

=IFERROR(VLOOKUP(A2,商品表,3,FALSE),”該当なし”)

XLOOKUP

=XLOOKUP(A2,コード列,単価列,”該当なし”)

短くなるだけでなく、読んで意味が分かる。どの列で探して、どの列を返すかが、式の中に書いてある。

ただし注意が1つ。XLOOKUPは新しい関数なので、古いバージョンのExcelでは使えない。社外に配るファイルや、共有サーバに置いて全員が触るファイルでは、相手の環境を確認してから使う。

INDEX＋MATCHという中間の選択肢

XLOOKUPが使えない環境では、INDEX＋MATCHがある。古くから使えて、VLOOKUPの弱点をだいたい解消できる。

=INDEX(返す列, MATCH(検索値, 探す列, 0))

• MATCHが「何行目にあるか」を返す
• INDEXが「その行の値」を返す

MATCHの3つめの引数「0」が完全一致を意味する。ここもVLOOKUPと同じで、省略すると1（近似一致）になるので、必ず書く。

分業になっているぶん、式は長い。ただ、探す列と返す列が独立しているので、列の挿入に強い。

世代でいうと、VLOOKUP → INDEX＋MATCH → XLOOKUP という流れになる。新しく書くならXLOOKUP、環境が古いならINDEX＋MATCH、既存のファイルを直すだけならVLOOKUPのままFALSEを足す。

「見つからない」を、どう扱うか

関数を変えるより大事かもしれないのが、ここだ。

見つからなかったときに、何を返すか。

• エラーのまま残す
• 「該当なし」と文字で返す
• 空欄にする
• 0を返す

0を返すのは、いちばん危ないと思う。合計に入ってしまうからだ。見つからなかったことが、集計の中に溶けて消える。

空欄も似た危険がある。あとで見た人が「まだ入力していない」のか「探したがなかった」のか区別できない。

「該当なし」と文字で書くのが、いちばん安全だと思う。集計にも入らないし、目で見て分かる。

そして、月次の作業なら「該当なし」の件数を数える式を1つ置いておく。

=COUNTIF(結果列,”該当なし”)

これが前月と大きく違ったら、元データが変わったサインになる。壊れたことに気づく仕掛けを、シートの中に置いておく。

一致しないときに疑う順番

VLOOKUPがエラーを返す。値はあるはずなのに。このときに見る順番を書いておく。

① 見えない空白

=LEN(A2) で長さを見る。想定より長ければ、前後に空白が入っている。TRIM関数で落とす。

② 全角と半角

「ABC123」と「ＡＢＣ１２３」は別物。ASC関数で半角に揃える。

③ 文字列と数値

「1001」（文字列）と 1001（数値）は一致しない。左寄せか右寄せかで見分ける。

④ 検索値が範囲の左端にあるか

VLOOKUPの基本を外している場合。これが意外と多い。

⑤ 範囲の絶対参照

式を下にコピーしたら範囲がずれていた、という事故。範囲はドル記号で固定するか、テーブルにして名前で参照する。

だいたい①〜③で片づく。浮動小数点の誤差を疑うのは、最後でいい。

テーブルにしておくと、ほとんど解決する

最後に、根本的な予防策を。

データの範囲をテーブル（Ctrl＋T）にしておくと、多くの問題が消える。

• 行を足すと、範囲が自動で広がる
• 列を指定するとき、列名で書ける（何列目か数えなくていい）
• 式をコピーしても、参照がずれない

XLOOKUPとテーブルを組み合わせると、こう書ける。

=XLOOKUP(A2, 商品表[コード], 商品表[単価], “該当なし”)

式を読むだけで、何をしているか分かる。 半年後の自分にも、引き継いだ人にも通じる。

VLOOKUPの第4引数を忘れるという事故は、結局式が読みにくいことから来ていると思う。読めば分かる形にしておけば、忘れても気づける。

航路 — 省略すると、近似になる

整理する。

• VLOOKUPの第4引数を省略するとTRUE（近似一致）になる
• 近似一致は「似たもの」ではなく「検索値を超えない最大の値」を返す
• だから見つからないときにエラーを出さず、違う値を返す
• 近似一致は昇順に並んでいることが前提。並んでいないと結果は不定
• 階段状の判定（送料、手数料）には有効。ただしTRUEを明示して書く
• XLOOKUPは既定が完全一致。左側も探せて、見つからない場合もその場に書ける
• 古い環境ならINDEX＋MATCH。MATCHの3つめも「0」を忘れない
• 見つからないときは「該当なし」と文字で返す。0は集計に溶ける

エラー表示は不親切に見えるけれど、「ない」と言ってくれているという意味では正直だ。

いちばん困るのは、ないものを「たぶんこれでしょう」と返されることだった。

そう考えると、この話は前に書いたAIのハルシネーションと同じ構図をしている。分からないときに分からないと言わない仕組みが、いちばん事故を起こす。

複数の条件で探したいとき

コードが1つなら、いままでの話で足りる。実務では「支店コードと商品コードの両方が一致する行」を探したいことがある。

やり方は3つある。

① 作業列で連結する

探す表に列を1本足して、=A2&”_”&B2 のように連結したキーを作る。検索値のほうも同じ形にして、その列で探す。

いちばん分かりやすく、いちばん速い。 作業列が増えるのが難点だが、あとから見た人にも通じる。

② XLOOKUPで、連結した範囲を指定する

=XLOOKUP(A2&”_”&B2, 支店列&”_”&商品列, 単価列, “該当なし”)

作業列を作らずに済む。ただし、内部で仮の配列を作るので、件数が多いと重くなる。

③ FILTER関数を使う

=FILTER(単価列, (支店列=A2)*(商品列=B2), “該当なし”)

条件を掛け算でつなぐ。複数該当した場合は、全部返ってくる。

1件だけ返ってくるはずなのに複数出た、という異常を見つけられるのが利点だと思う。VLOOKUPは黙って最初の1件を返すので、重複に気づけない。

重複に、気づく仕掛け

いまの話の続き。VLOOKUPもXLOOKUPも、該当が複数あっても最初の1件を返す。

マスタに同じコードが2行あることに、誰も気づかない。そして単価が違っていたりする。

だから、マスタ側に重複チェックの列を1本入れておく。

=COUNTIF(コード列, A2)

これが2以上になっている行があれば、重複している。条件付き書式で色を付けておけば、開いた瞬間に分かる。

マスタは、重複がないことが前提の表だ。前提が崩れたときに気づける仕掛けを、表の中に置いておく。

検索の速さの話

参考までに、速度の傾向も書いておく。

• 完全一致のVLOOKUP／XLOOKUP　→ 上から順に探す（線形探索）
• 近似一致のVLOOKUP　→ 二分探索。圧倒的に速い

だから、大量データで速度が問題になるときは「並べ替えて近似一致」という手がある。ただし、前に書いたとおり見つからないときに違う値を返すので、危ない。

安全に速くするなら、

• XLOOKUPの検索モードを指定する（二分探索を選べる）
• 範囲を列全体でなく、必要な行数に絞る
• テーブルにして、範囲を自動で最小にする

そして、そもそも数万件を関数で引くなら、Power Queryのマージを使うほうが速い。関数は「1件ずつ探す」のを繰り返すが、Power Queryはまとめて突き合わせる。

件数が増えたら道具を変える。これはExcel全般に言えることだと思う。

式を読みやすくしておく

最後に、事故を減らすいちばん効く方法を。

範囲に名前を付ける。

数式タブの「名前の定義」で、A2:C500 に「商品マスタ」と名前を付ける。するとこう書ける。

=XLOOKUP(A2, 商品コード, 商品単価, “該当なし”)

$マークも、列番号も出てこない。式が日本語で読める。

テーブルにすれば、名前は自動で付く。商品表[コード] のような書き方になる。

半年後に自分が見たとき、あるいは引き継いだ人が見たとき、式を読むだけで意図が分かる。第4引数を書き忘れたかどうかも、読めば分かる。

間違いを防ぐ最良の方法は、間違いが見える形にしておくことだと思う。

この記事は、まだ書きかけです

この記事は、公開してから足していく形にしています。調べたことを書いた本文がまず先にあって、あとから「聞き手と話し手の会話」「あとで思い出したこと」「読んだ人から教わった補足」が積もっていきます。

だから、同じURLでも半年後には別のものになっているはずです。もし一度読んだことがある記事なら、下の更新ログだけ見てください。増えたところが分かります。

更新ログ

• 2026-10-09　公開（本文のみ。会話パートはこれから足します）