Excel約5分で読めます

ExcelのVLOOKUPで近似一致になっていた|FALSEの指定を全数式で確認

ExcelのVLOOKUPで近似一致になっていたを表したイメージ
記事の内容を表したイメージ(AI生成)

VLOOKUPが返す値がおかしい、なぜか別の行のデータが引っ張られている、という状態のとき、第4引数(検索の型)が「近似一致」になっているケースが原因の一つとして挙げられます。この引数を省略するかTRUEを指定すると「近似一致」として動作し、意図しない行の値を返すことがあります。

この記事では、近似一致と完全一致の違い、FALSEを指定すべき理由、ブック内の全VLOOKUPを短時間で確認・修正する手順を説明します。「なぜ間違った値が返るのか」と「どこを直せばよいか」を順に整理しますので、今日の締切前に確認したい方はまず次の節をご覧ください。


近似一致と完全一致で何が変わるか

VLOOKUPの構文は次のとおりです。

=VLOOKUP(検索値, 範囲, 列番号, 検索の型)

第4引数「検索の型」にはFALSE(完全一致)かTRUEまたは省略(近似一致)を指定します。

完全一致(FALSE) は、検索値と一字一句同じセルだけを探します。一致するものがなければ#N/Aエラーを返します。

近似一致(TRUEまたは省略) は、検索値以下の最大値を返す仕組みです。このモードが正しく機能するには、検索列が昇順に並んでいる必要があります。並んでいない場合は、どの行が返るか予測できません。

事務の現場で使う名簿・申込一覧・商品マスタなどは、多くの場合「完全に一致するコードや名前を引きたい」という目的です。近似一致が役に立つのは、税率テーブルや送料テーブルのように「〇〇以上〇〇未満はこの値」という範囲検索をするときに限られます。それ以外の場面ではFALSEを使うのが原則です。


なぜ省略が混入しやすいのか

近似一致になってしまう原因として多いのは次の二つです。

  • 引数を省略した:VLOOKUPは第4引数を省略できるため、テンプレートや他者から受け取ったファイルに最初から書かれていないことがあります。省略時はTRUEと同じ動作になります。
  • `1`と`0`で書かれている:TRUEの代わりに1、FALSEの代わりに0が使われていることがあります。1は近似一致なので、FALSEと思い込んで確認を飛ばすと見落とします。

また、数式をコピーしたときに第4引数だけ変わることはありませんが、元の数式がそもそも省略されていれば、コピー先もすべて近似一致のままになります。


FALSEが必要かどうかの判断基準

修正前に、その数式が近似一致を意図しているかどうかを確認します。

表は横にスクロールできます

使い方の例近似一致でよいか
社員番号で氏名を引く不要。FALSEにする
商品コードで単価を引く不要。FALSEにする
郵便番号で都道府県を引く不要。FALSEにする
売上金額で送料テーブルを引く(段階的な値)意図的な近似一致の可能性あり
点数で評価ランクを引く(〇点以上はA等)意図的な近似一致の可能性あり

送料テーブルや評価ランクのような段階的な値を引いているVLOOKUPは、近似一致が正しい設計の場合があります。そのセルを変更する前に、その数式が何を目的にしているか、隣接するデータの意味を確認してください。


ブック内の全VLOOKUPを確認する手順

作業の前にファイルをコピーしてバックアップを取ってください。「名前をつけて保存」で別名のファイルを作り、元のファイルを閉じた状態で以下を行うと安全です。

検索で全VLOOKUPを一覧する

  1. Ctrl + Fで「検索と置換」を開きます。
  2. 検索文字列に VLOOKUP と入力します。
  3. 「オプション」を展開し、「シート」を「ブック」に変更します。
  4. 「すべて検索」を押すと、ブック内の全VLOOKUPが一覧に表示されます。
  5. 一覧の各行をクリックすると、そのセルに移動できます。

画面の表示は版や設定により異なります。メニュー名が見当たらない場合は公式のヘルプで確認してください。

数式バーで第4引数を確認する

移動したセルの数式バーを見て、第4引数を確認します。

  • ,FALSE) または ,0) で終わっている → 完全一致。問題なし。
  • ,TRUE) または ,1) で終わっている → 近似一致。意図的かどうかを確認。
  • 第4引数がない(,列番号) で終わっている) → 近似一致と同じ動作。

架空の例で確認します(架空の例です)。

=VLOOKUP(A2, 社員マスタ!$A:$C, 2)      ← 省略。近似一致と同じ
=VLOOKUP(A2, 社員マスタ!$A:$C, 2, 1)  ← 1はTRUE。近似一致
=VLOOKUP(A2, 社員マスタ!$A:$C, 2, 0)  ← 0はFALSE。完全一致
=VLOOKUP(A2, 社員マスタ!$A:$C, 2, FALSE) ← 完全一致

社員番号で氏名を引く目的なら、上の三つはFALSEまたは0に修正します。

置換で一括修正する(同じパターンの場合)

完全一致に直したいVLOOKUPがすべて同じパターンで省略されている場合は、「検索と置換」の置換機能を使えます。ただし、近似一致を意図したVLOOKUPが混在している場合は一括置換を使わず、一件ずつ確認してください。

  1. Ctrl + Hで「検索と置換」を開きます。
  2. 検索文字列に ,社員マスタ!$A:$C,2) のように末尾の ) を含めて入力します。
  3. 置換後の文字列に ,社員マスタ!$A:$C,2,FALSE) と入力します。
  4. 「すべて置換」の前に「次を検索」で対象が正しいか確認してから実行します。

範囲や列番号がシートごとに異なる場合は、一括置換を使わずシートごとに確認する方が確実です。

修正後に値を確認する

修正した数式が正しい値を返しているか確認します。

  • #N/Aが出た場合:検索値が参照先のリストに存在しないことを意味します。完全一致に直したことで、これまで誤った行の値を返していたものが正直にエラーを出すようになった状態です。検索値かマスタデータのどちらかに表記のずれがないか確認してください。
  • 以前と同じ値が返った場合:元々の検索値と参照先が一致していたか、近似一致でたまたま正しい行を返していたかのどちらかです。いずれにせよFALSE指定が正しい状態です。
  • 以前と違う値が返った場合:近似一致のときに誤った行を参照していたことになります。どちらが正しい値かをマスタデータと突き合わせて確認してください。

迷いやすい例外:意図的な近似一致を壊さないために

先に述べた送料テーブルや評価ランクのテーブルは、近似一致が設計上の意図である場合があります。こうした数式は第4引数がTRUEや省略でも問題ありません。

見分け方として、参照先の表の一列目が連番・コード・名前ではなく「0, 1000, 3000, 5000」のような数値の閾値(しきい値)になっているかどうかを確認します。閾値テーブルを参照しているVLOOKUPは、近似一致のまま残します。

一方、参照先が社員マスタ・商品マスタ・取引先一覧のような一意のコードや名前のリストであれば、完全一致が正しい使い方です。


次の行動

今日中に確認が必要であれば、まずファイルをコピーしてバックアップを取り、Ctrl + Fでブック全体のVLOOKUPを一覧してください。社員マスタや商品マスタを参照している数式の第4引数を確認し、省略・1・TRUEになっているものをFALSEまたは0に直します。修正後は#N/Aが出たセルの検索値とマスタを突き合わせ、表記のずれがないかを確認してください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ