Excelの近似値検索を完全攻略！7つの関数テクニックで業務が劇変する

「Excelで近似値を検索したいのに、思った通りの結果が出ない……」そんな経験はありませんか？たとえば、料金表から最適な価格帯を自動で引っ張りたい、成績表からランクを自動判定したい、在庫リストから基準値にもっとも近い商品を見つけたい。こうした場面でつまずくExcelユーザーは、実はとても多いのです。

近似値検索は、完全一致検索とは違い「ぴったり同じ値がなくても、一番近い値を探し出す」テクニックです。料金テーブル、税率表、送料表、成績のランク付けなど、ビジネスの現場では完全一致よりもむしろ近似値で検索したい場面のほうが多いかもしれません。

この記事では、VLOOKUP関数の基本から最新のXLOOKUP関数、さらにINDEXとMATCHの組み合わせやABS関数を使った「真に一番近い値」を見つけるテクニックまで、Excelの近似値検索を徹底的に解説します。2026年最新のCopilot連携情報も盛り込んでいますので、初心者から上級者まで必ず新しい発見があるはずです。

ここがポイント！

VLOOKUPからXLOOKUPまで、近似値検索の仕組みと正しい使い方の全体像
「以上で最小」「以下で最大」「絶対値で最近接」など目的別の数式テクニック7選
2026年最新のCopilot関数やFILTER関数を活用した次世代の近似値検索手法

そもそもExcelの近似値検索とは何か？
VLOOKUPの近似値検索を正しく理解する
XLOOKUPで近似値検索が劇的に進化した
INDEXとMATCHの組み合わせで近似値を自在に操る
1. VLOOKUPにはできない「左方向検索」
2. 大量データでの高速VLOOKUPテクニック
「本当に一番近い値」を探すABSとMINの配列数式
1. 配列数式の確定方法に注意
2. LET関数と組み合わせてスマートに書く
2026年最新のExcel近似値検索事情とCopilotの活用
実務で役立つ近似値検索の応用パターン
テーブル化で近似値検索の数式がグッと見やすくなる
情シス歴12年の現場で叩き込まれた近似値検索の落とし穴
現場で即使えるVBAマクロ集近似値検索を支える裏方コード
誰も教えてくれなかった実務トラブルと体験ベースの解決法
知っておくと差がつく近似値検索の裏テクニック
ぶっちゃけこうした方がいい！
Excelの近似値検索に関するよくある疑問を解決
今すぐパソコンやスマホの悩みを解決したい！どうしたらいい？
まとめ

そもそもExcelの近似値検索とは何か？

Excelのイメージ

Excelにおける近似値検索とは、検索したい値と完全に一致するデータがない場合に、もっとも近い値を代わりに返してくれる検索方法のことです。英語では「Approximate Match」と呼ばれ、VLOOKUPやXLOOKUP、MATCH関数などで利用できます。

たとえば、送料表で「重量5kgの送料はいくら？」と検索したいとします。でも送料表には1kg、3kg、10kgの区分しかありません。このとき完全一致で検索すると「該当なし」になってしまいますが、近似値検索を使えば「5kg以下で最大の3kg」の送料を自動で返してくれるわけです。これが近似値検索の基本的な考え方です。

ただし、ここで注意すべきポイントがあります。「近似値」と一口に言っても、実はExcelの関数によって動作がまったく異なるのです。大きく分けると次の3パターンがあります。

近似値の種類	意味	代表的な利用場面
検索値以下の最大値	検索値を超えない範囲で一番近い値を返す	料金表、税率表、送料区分
検索値以上の最小値	検索値を下回らない範囲で一番近い値を返す	容量の選定、サイズの決定
絶対値で最も近い値	上下関係なく、差の絶対値が最小の値を返す	測定値の照合、データマッチング

このどれを求めているかによって、使うべき関数や数式の組み立て方が変わってきます。以降の章で、それぞれの方法を具体的に見ていきましょう。

VLOOKUPの近似値検索を正しく理解する

Excelで近似値検索といえば、まず思い浮かぶのがVLOOKUP関数です。VLOOKUP関数の第4引数にTRUEを指定する（あるいは省略する）と、近似値検索モードになります。構文を確認しておきましょう。

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

この近似値検索モードでは、「検索値以下の最大値」を返します。つまり、検索値をちょうど超えない範囲でもっとも大きな値を探し出し、その行の指定列にある値を返してくれるのです。

昇順ソートが絶対条件である理由

VLOOKUPの近似値検索には、見落とされがちだけど致命的な前提条件があります。それは検索範囲の最左列が必ず昇順（小さい順）に並んでいなければならないということです。

なぜかというと、VLOOKUPの近似値検索は内部的にバイナリサーチ（二分探索）というアルゴリズムを使っています。これは「データが順番に並んでいること」を前提として高速に検索する手法です。ソートされていないデータに対してバイナリサーチを行うと、まったく意味のないデタラメな結果が返ってきます。エラーにすらならないので、間違いに気づかないまま誤ったデータで業務を進めてしまう危険があるのです。

実際の業務で「VLOOKUPの結果がなぜかおかしい」というトラブルの原因は、多くの場合この昇順ソート忘れです。近似値検索を使うときは、まず検索範囲を昇順で並べ替えてから数式を入力するクセをつけましょう。

省略時のデフォルトがTRUEという罠

もう一つ、VLOOKUPで頻繁にトラブルを起こすポイントがあります。第4引数を省略したとき、自動的にTRUE（近似値検索）になるという仕様です。完全一致のつもりで第4引数を書き忘れると、意図せず近似値検索が実行され、見当違いの値が返ってくることがあります。

完全一致検索をしたいときは必ずFALSEを明示的に指定する、これはExcelを使ううえでの鉄則です。逆に言えば、近似値検索をしたいときはTRUEを省略しても動作しますが、可読性のためにあえてTRUEと書いておくことをおすすめします。

VLOOKUPの近似値検索が文字列に対してどう動くのか

数値での近似値検索はイメージしやすいですが、文字列の場合はどうでしょうか？実は文字列に対してVLOOKUPの近似値検索を実行すると、前方一致のような動作をします。ただし少し特殊で、「検索値が範囲の値と前方一致しているか」ではなく、「範囲の各値が検索値に対して前方一致しているか」という方向性になります。

たとえば検索値が「東京都渋谷区」の場合、範囲に「東京」「東京都」「東京都新宿区」があると、より多くの文字が一致する「東京都」が返される仕組みです。ただし、これも昇順に並んでいることが大前提であり、文字列のソート順序を間違えると正しい結果が得られません。文字列での近似値検索は予測しにくい挙動になりがちなので、基本的には完全一致検索を使い、近似値検索は数値データに対して使うのが安全です。

XLOOKUPで近似値検索が劇的に進化した

VLOOKUPの弱点を根本から解消した関数が、Microsoft 365で利用できるXLOOKUP関数です。2020年頃から順次追加され、いまやExcelの検索関数の主役といえる存在になりました。

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

XLOOKUPが近似値検索で革命的なのは、第5引数の一致モードで「次に小さい値」と「次に大きい値」のどちらも選べるようになった点です。

一致モードの値	動作	VLOOKUPとの比較
0（デフォルト）	完全一致のみ。見つからなければエラー	VLOOKUPのFALSEに相当
-1	完全一致、なければ次に小さい値を返す	VLOOKUPのTRUEに相当
1	完全一致、なければ次に大きい値を返す	VLOOKUPでは不可能だった機能
2	ワイルドカード一致（部分一致検索）	VLOOKUPでは暗黙的に有効だった

昇順ソート不要で近似値検索ができる

XLOOKUPの一致モード「-1」や「1」を使った近似値検索では、検索範囲が昇順に並んでいなくても正しい結果が返るのが大きな特長です。VLOOKUPの近似値検索で散々悩まされた「ソート忘れ問題」から完全に解放されます。

ただし、XLOOKUPにも検索モード2（バイナリサーチ昇順）や-2（バイナリサーチ降順）を指定した場合は、データが正しくソートされていないと不正確な結果になります。通常の検索モード（1または-1）であれば、先頭から順に検索してくれるので安心です。大量データでバイナリサーチによる高速化を図りたい場合だけ、ソートに注意すればよいでしょう。

「検索値以上の最小値」がワンステップで取れる

VLOOKUPでは「検索値以下の最大値」しか取れませんでした。たとえばバッグに入れたい物のサイズから「その寸法が入る最小のバッグ」を探すといった「検索値以上の最小値」を求めるケースでは、VLOOKUPだけでは対応できず、IFERRORやINDEX＋MATCHを組み合わせた複雑な数式が必要でした。

XLOOKUPなら、一致モードに1を指定するだけで「次に大きい値」を返してくれます。たとえば送料表で「重量5kgに対応する、5kg以上で最も安い送料区分」を探すには、こう書くだけです。

=XLOOKUP(5, 重量範囲, 送料範囲, “該当なし”, 1)

この一行で済むのは、VLOOKUPしか使えなかった時代を知っている人からすると衝撃的な進化です。

エラー処理がスマートになった

VLOOKUPでは検索値が見つからなかったとき、問答無用で#N/Aエラーが表示されました。これを回避するためにはIFERROR関数で囲む必要があり、数式が冗長になっていました。

XLOOKUPでは第4引数に「見つからない場合の表示値」を直接指定できるので、IFERROR関数を使わなくてもスッキリした数式でエラー対応が可能です。たとえば「該当なし」や空文字””を設定するだけで、表の見栄えが格段に良くなります。

INDEXとMATCHの組み合わせで近似値を自在に操る

VLOOKUP以前から上級者に愛用されてきたのが、INDEX関数とMATCH関数の組み合わせです。XLOOKUPが使えない環境（Excel 2016やExcel 2019）では、いまでもこの組み合わせが最強の選択肢です。

=INDEX(戻り範囲, MATCH(検索値, 検索範囲, 照合の種類))

MATCH関数の第3引数が近似値検索のカギを握っています。1を指定すると「検索値以下の最大値」の位置を返し、-1を指定すると「検索値以上の最小値」の位置を返します。0は完全一致です。

VLOOKUPにはできない「左方向検索」

VLOOKUPは検索列が必ず範囲の左端になければならないという制約がありました。INDEX＋MATCHにはこの制約がなく、検索列と戻り列を自由に指定できます。検索値が右端にある表でも問題なく近似値検索ができるのです。

XLOOKUPが登場した今でも、Excel 2016や2019を使っているチームとファイルを共有する場合には、互換性の高いINDEX＋MATCHのほうが安定した選択肢です。

大量データでの高速VLOOKUPテクニック

数十万行を超える大量データに対してVLOOKUPの完全一致検索を大量のセルに設定すると、再計算に非常に長い時間がかかることがあります。この場合の裏技として、MATCH関数の近似値検索（第3引数＝1）を活用した高速化が知られています。

仕組みとしては、まずMATCH関数で近似値検索（バイナリサーチ）を行って候補の位置を特定し、次にINDEX関数でその位置の値を取得し、最後に取得した値が本当に検索値と一致しているかをIF関数で判定します。近似値検索はバイナリサーチのおかげで完全一致検索よりも圧倒的に高速なので、この手法で再計算時間を大幅に短縮できます。ただし、この場合も検索範囲を昇順にソートしておくことが必須条件です。

「本当に一番近い値」を探すABSとMINの配列数式

ここまで紹介したテクニックは、すべて「以上」か「以下」のどちらか一方向で近い値を探すものでした。しかし実務では「上でも下でもいいから、とにかく一番近い値がほしい」というケースもあります。たとえば、測定値に最も近い基準値を見つけたいとか、目標値に一番近い実績データを抽出したいといった場面です。

この場合に使えるのが、ABS関数、MIN関数、INDEX関数、MATCH関数を組み合わせた配列数式です。

=INDEX(データ範囲, MATCH(MIN(ABS(データ範囲-基準値)), ABS(データ範囲-基準値), 0))

この数式の仕組みを分解して説明しましょう。まずABS(データ範囲-基準値)で、各データと基準値の差の絶対値を計算します。次にMIN関数でその絶対値の中から最小値を見つけます。そしてMATCH関数でその最小値が何番目にあるかを特定し、最後にINDEX関数でその位置にある元のデータを返します。

配列数式の確定方法に注意

この数式は配列数式なので、Excel 2019以前のバージョンでは入力後にCtrl＋Shift＋Enterキーの同時押しで確定する必要があります。正しく確定すると、数式バーで数式が波括弧 { } で囲まれて表示されます。Microsoft 365やExcel 2021以降では、通常のEnterキーだけで配列数式として自動認識されるので、特別な操作は不要です。

LET関数と組み合わせてスマートに書く

Microsoft 365をお使いなら、LET関数とXLOOKUP関数を組み合わせることで、同じことをより読みやすく書けます。

=LET(差分, ABS(データ範囲-基準値), XLOOKUP(MIN(差分), 差分, データ範囲))

LET関数を使うと、ABS(データ範囲-基準値)という同じ計算を2回書く必要がなくなり、「差分」という名前で一度だけ定義して使い回せます。可読性が上がるだけでなく、計算の無駄も減るので一石二鳥です。

2026年最新のExcel近似値検索事情とCopilotの活用

2026年に入り、ExcelはAIとの統合がさらに加速しています。とくに注目すべきなのが、2025年末からBetaチャンネルで展開が始まったCOPILOT関数です。

COPILOT関数でセル内からAIに指示が出せる

COPILOT関数は、=COPILOT(“指示文”, データ範囲)という構文で、セルの中から直接AIに処理を依頼できる画期的な機能です。これまでCopilotはExcelの横に表示されるサイドパネルから使うものでしたが、COPILOT関数の登場により、数式の一部としてAIを組み込めるようになりました。

たとえば、テキストデータの分類やセンチメント分析、非構造化データからの情報抽出などが得意分野です。直接的な近似値検索を代替するものではありませんが、「どの関数を使えばいいか分からない」というときにCopilotのサイドパネルで相談すると、適切な数式を提案してくれます。

Agent ModeでExcelの操作そのものをAIが代行

2026年1月にデスクトップ版でも展開されたAgent Modeは、Copilotが複数のステップにわたる作業を自律的に計画・実行してくれる機能です。たとえば「この売上データから地域別の料金テーブルを作成して、各注文に最適な送料区分をXLOOKUPで割り当ててほしい」といった複合的なタスクを、AIが一連のステップとして処理してくれます。

Agent Modeは2026年2月時点ではMicrosoft 365 Copilotライセンスが必要ですが、数式作成の自動化という意味では近似値検索のハードルを大きく下げてくれる可能性があります。

数式の自動補完機能がさらに進化

2025年11月にWindows版で展開されたFormula Completion（数式自動補完）機能も見逃せません。セルに「=」を入力した瞬間から、ワークシートのコンテキスト（ヘッダー、隣接セル、テーブル構造など）をCopilotが分析して、適切な数式をリアルタイムで提案してくれます。VLOOKUPの近似値検索を書こうとしているときに、XLOOKUPのほうが適切だと判断されれば、XLOOKUPの数式が候補に表示される仕組みです。

実務で役立つ近似値検索の応用パターン

料金表や税率表からの自動区分判定

もっとも典型的な活用場面は、累進的な料金テーブルからの値の取得です。所得税の速算表、送料表、コミッション率の表など、「境界値から判定する」タイプの計算は、すべてXLOOKUPの一致モード-1（次に小さい値）で解決します。VLOOKUPのTRUEでも同じことができますが、昇順ソートが不要なXLOOKUPのほうが安全です。

検索値以上で最も近いバッグを選ぶパターン

逆方向の近似値、つまり「検索値以上の最小値」が必要な場面もよくあります。たとえば、書類のサイズに合う最小のバッグを選ぶ、ファイル容量に合う最安のストレージプランを選ぶといったケースです。VLOOKUPだけでは対応できなかったこのパターンも、XLOOKUPなら一致モード1で一発です。

もしXLOOKUPが使えない環境であれば、VLOOKUPの完全一致とINDEX＋MATCH（第3引数1）に＋1を足す手法を組み合わせ、IFERRORで段階的に処理する方法があります。数式はやや複雑になりますが、確実に動作します。

=IFERROR(VLOOKUP(検索値,範囲,列番号,FALSE), IFERROR(INDEX(戻り範囲, MATCH(検索値,検索範囲,1)+1), “OVER”))

最初のVLOOKUPで完全一致を試み、見つからなければMATCHの近似値検索で「検索値以下の最大値」の位置を取得し、そこに+1することで「検索値を超える最初の値」を取得するという発想です。それもエラーなら「OVER」と表示させる三段構えの数式になっています。

複数条件での近似値検索

XLOOKUPとIF関数、あるいはFILTER関数を組み合わせると、複数の条件を満たしたうえで近似値検索をかけることも可能です。たとえば「配送サービスが2日便で、重量72kgに最も近い料金」を求めるには次のように書きます。

=XLOOKUP(重量セル, IF(サービス列=サービス条件, 重量列), 料金列, , -1)

IF関数でサービスの条件に合致しない行をFALSEに置き換え、XLOOKUPにはサービス条件を満たす行だけを見せるイメージです。FILTER関数を使う方法もあり、直感的にはFILTER版のほうが分かりやすいかもしれませんが、FILTERは検索範囲と戻り範囲の両方に適用する必要があるため数式が長くなります。

テーブル化で近似値検索の数式がグッと見やすくなる

Excelの表をテーブルとして書式設定しておくと、XLOOKUPやVLOOKUPの引数でセル範囲の代わりにテーブル名と列名を使って指定できるようになります。

テーブル化していない場合と、「料金表」という名前でテーブル化した場合の数式を比べてみましょう。

テーブル化なし=XLOOKUP(A2, $D$2:$D$20, $E$2:$E$20, “該当なし”, -1)

テーブル化あり=XLOOKUP(A2, 料金表, 料金表, “該当なし”, -1)

どちらが読みやすいかは一目瞭然です。テーブル化すれば絶対参照（$マーク）も不要になりますし、テーブルにデータ行を追加すると数式の参照範囲が自動で拡張されます。近似値検索に限らず、Excelで表を扱うならテーブル化はぜひ習慣にしてほしいテクニックです。

情シス歴12年の現場で叩き込まれた近似値検索の落とし穴

Excelのイメージ

ここからは、私が情報システム部門で12年以上にわたって社内のExcelトラブルに対応してきた中で、何度も繰り返し遭遇した「教科書には載っていない」近似値検索の地雷について語ります。関数の構文を覚えるだけでは絶対に避けられない、現場特有のリアルな問題ばかりです。

数値に見えて数値じゃないセルが引き起こす静かな破壊

近似値検索のトラブルで、体感として最も多いのがこれです。見た目は完全に数字なのに、セルの中身が文字列として格納されているパターン。基幹システムやCSVからエクスポートしたデータに特に多く、セルの左上に小さな緑色の三角マーク（エラーインジケーター）が出ているのが目印です。

VLOOKUPでもXLOOKUPでも、検索値が数値なのに検索範囲のデータが文字列として入っていると、完全一致はもちろん近似値検索でも正しくヒットしません。しかもエラーにならず、#N/Aが返るか、あるいは見当違いの値が返ってくるだけなので、原因の特定に異常に時間がかかります。

現場での見分け方は単純で、疑わしいセルを選択してTYPE関数で確認します。=TYPE(A2)の結果が1なら数値、2なら文字列です。あるいは=ISNUMBER(A2)でTRUE/FALSEを見るのも手っ取り早い方法です。解決策としては、検索範囲のデータに対して=A2*1や=VALUE(A2)で数値に変換するか、後述するVBAで一括変換するのが実務的です。

非表示行が近似値検索の結果を狂わせる問題

これも情シスあるあるです。誰かがフィルターをかけたまま保存したExcelファイルで近似値検索を使うと、非表示の行も含めて検索されるため、見た目上は昇順に並んでいるのに実際のデータ順序は昇順ではなく、VLOOKUPの近似値検索が誤動作するというケースです。

対策としては、近似値検索を組む前に必ずすべてのフィルターを解除してからデータのソート状態を確認すること。加えて、手動で行を非表示にしている場合も同様の問題が起きるので、Ctrl＋Shift＋9で非表示行を再表示してから検証する習慣をつけましょう。XLOOKUPの検索モード1（先頭から順に検索）を使っている場合は、ソート順に依存しないため、この問題は発生しにくくなります。

共有ブックで近似値検索の参照範囲が壊れる現象

複数人で同時編集する共有ブック環境（レガシー共有機能）では、マスターテーブルに行を追加した際に他のユーザーのVLOOKUP数式の参照範囲が正しく拡張されないことがあります。テーブル化していれば自動で拡張されますが、テーブル化せずにセル範囲で指定していると、新しく追加されたデータが検索範囲外になって#N/Aが出るのです。

2026年現在はMicrosoft 365の共同編集機能が主流になりつつありますが、まだレガシーな共有ブックを使い続けている現場は少なくありません。根本的な対策はテーブル化ですが、それが難しい場合は参照範囲を列全体指定（例$A:$A）にしておくとこの問題を回避できます。ただし列全体指定は計算速度が低下するトレードオフがあるので、データ量が数万行を超える場合はテーブル化を強くおすすめします。

現場で即使えるVBAマクロ集近似値検索を支える裏方コード

ここでは、近似値検索を正しく機能させるために現場で頻繁に使うVBAマクロを紹介します。すべてExcel 2016、2019、2021、Microsoft 365（Windows版）で動作検証済みです。Mac版Excelでは一部のマクロが動作しない可能性があるため、Windows環境での利用を前提としてください。

文字列として格納された数値を一括で数値に変換するVBA

近似値検索トラブルの元凶である「文字列数値」を一掃するマクロです。選択範囲内のセルを走査し、文字列として格納されているが数値に変換可能なセルをすべて数値型に変換します。

Sub ConvertTextToNumber()

    '選択範囲内の文字列数値を数値型に一括変換

    '動作確認: Excel 2016 / 2019 / 2021 / Microsoft 365 (Windows)

    Dim cell As Range

    Dim convertCount As Long

    convertCount = 0
    If Selection Is Nothing Then

        MsgBox "変換したい範囲を選択してから実行してください。", vbExclamation

        Exit Sub

    End If
    Application.ScreenUpdating = False
    For Each cell In Selection

        If Not IsEmpty(cell) Then

            If Not IsError(cell.Value) Then

                If VarType(cell.Value) = vbString Then

                    If IsNumeric(cell.Value) Then

                        Dim temp As String

                        temp = cell.Value

                        cell.Clear

                        cell.Value = CDbl(temp)

                        convertCount = convertCount + 1

                    End If

                End If

            End If

        End If

    Next cell

Application.ScreenUpdating = True MsgBox convertCount & " 件のセルを数値に変換しました。", vbInformation End Sub

使い方は簡単で、変換したいセル範囲を選択してからマクロを実行するだけです。CSVインポート直後のデータや、基幹システムから貼り付けたデータに対して定期的に実行しておくと、近似値検索の誤動作を未然に防げます。

近似値検索用にデータが正しく昇順ソートされているか検証するVBA

VLOOKUPの近似値検索を使うなら、検索範囲が昇順かどうかの事前検証は必須です。目視ではミスが出やすいので、マクロで自動チェックしましょう。

Sub CheckAscendingOrder()

    '選択範囲が昇順に並んでいるか検証する

    '動作確認: Excel 2016 / 2019 / 2021 / Microsoft 365 (Windows)

    Dim rng As Range

    Dim i As Long

    Dim errorRows As String

    Dim errorCount As Long
    Set rng = Selection
    If rng.Columns.Count > 1 Then

        MsgBox "1列のみ選択してください。", vbExclamation

        Exit Sub

    End If

If rng.Rows.Count < 2 Then MsgBox "2行以上選択してください。", vbExclamation Exit Sub End If errorCount = 0 For i = 2 To rng.Rows.Count If Not IsEmpty(rng.Cells(i, 1)) And Not IsEmpty(rng.Cells(i - 1, 1)) Then If Not IsError(rng.Cells(i, 1).Value) And Not IsError(rng.Cells(i - 1, 1).Value) Then If rng.Cells(i, 1).Value < rng.Cells(i - 1, 1).Value Then errorCount = errorCount + 1 rng.Cells(i, 1).Interior.Color = RGB(255, 200, 200) If errorCount <= 10 Then errorRows = errorRows & "行 " & rng.Cells(i, 1).Row & vbCrLf End If End If End If End If Next i If errorCount = 0 Then MsgBox "検証完了：選択範囲は正しく昇順に並んでいます。", vbInformation Else MsgBox "昇順違反が " & errorCount & " 件見つかりました。" & vbCrLf & _ "該当セルをピンク色で着色しました。" & vbCrLf & vbCrLf & _ "最初の違反箇所:" & vbCrLf & errorRows, vbWarning End If End Sub

このマクロは昇順に並んでいない箇所を検出すると、そのセルの背景をピンク色に着色してくれます。何万行もあるデータで「どこかのソートが崩れているかもしれない」という不安を一瞬で解消できるので、VLOOKUP近似値検索を多用するシートの保守に重宝します。

指定値に最も近い値をユーザー定義関数として使えるVBA

ABS＋MIN＋INDEX＋MATCHの配列数式は強力ですが、毎回書くのは正直面倒です。以下のVBAをモジュールに登録すると、=NEARESTVALUE(基準値, データ範囲)というシンプルな数式で「絶対値で最も近い値」を取得できるようになります。

Function NEARESTVALUE(target As Double, dataRange As Range) As Variant

    '指定した基準値に絶対値で最も近い値を返すユーザー定義関数

    '動作確認: Excel 2016 / 2019 / 2021 / Microsoft 365 (Windows)

    Dim cell As Range

    Dim minDiff As Double

    Dim closestVal As Variant

    Dim currentDiff As Double
    minDiff = 1E+99

    closestVal = CVErr(xlErrNA)

For Each cell In dataRange If IsNumeric(cell.Value) And Not IsEmpty(cell) Then currentDiff = Abs(cell.Value - target) If currentDiff < minDiff Then minDiff = currentDiff closestVal = cell.Value End If End If Next cell NEARESTVALUE = closestVal End Function

このユーザー定義関数の優れた点は、空白セルやエラーセルを自動でスキップしてくれることです。配列数式だとデータ範囲に空白やエラーが含まれていると#VALUE!エラーになることがありますが、VBAなら堅牢にハンドリングできます。

近似値検索のトラブル原因を一括診断するVBA

情シスとして何百件ものトラブル対応をしてきた結果、たどり着いた「これさえ走らせればだいたい原因が分かる」という診断マクロです。選択範囲のデータ型の混在状況、ソート状態、空白セルの有無、エラーセルの有無をまとめてレポートします。

Sub DiagnoseLookupRange()

    '近似値検索用データ範囲の健全性を一括診断する

    '動作確認: Excel 2016 / 2019 / 2021 / Microsoft 365 (Windows)

    Dim rng As Range

    Dim cell As Range

    Dim numCount As Long, strCount As Long, emptyCount As Long, errCount As Long

    Dim strNumCount As Long

    Dim isSorted As Boolean

    Dim prevVal As Variant

    Dim report As String
    Set rng = Selection

    numCount = 0: strCount = 0: emptyCount = 0: errCount = 0: strNumCount = 0

    isSorted = True

    prevVal = Empty
    For Each cell In rng

        If IsEmpty(cell) Then

            emptyCount = emptyCount + 1

        ElseIf IsError(cell.Value) Then

            errCount = errCount + 1

        ElseIf VarType(cell.Value) = vbString Then

            strCount = strCount + 1

            If IsNumeric(cell.Value) Then

                strNumCount = strNumCount + 1

            End If

        ElseIf IsNumeric(cell.Value) Then

            numCount = numCount + 1

            If Not IsEmpty(prevVal) And IsNumeric(prevVal) Then

                If cell.Value < prevVal Then isSorted = False
            End If
            prevVal = cell.Value
        End If
    Next cell
    
    report = "===== 近似値検索データ診断レポート =====" & vbCrLf & vbCrLf
    report = report & "■ セル総数: " & rng.Cells.Count & vbCrLf
    report = report & "■ 数値セル: " & numCount & vbCrLf
    report = report & "■ 文字列セル: " & strCount
    If strNumCount > 0 Then

        report = report & " (うち数値変換可能: " & strNumCount & " 件 ← 要修正！)"

    End If

    report = report & vbCrLf

    report = report & "■ 空白セル: " & emptyCount & vbCrLf

    report = report & "■ エラーセル: " & errCount & vbCrLf & vbCrLf
    If strNumCount > 0 Then

        report = report & "【警告】文字列として格納された数値が " & strNumCount & " 件あります。" & vbCrLf

        report = report & "　→ 近似値検索が正しく動作しない原因になります。" & vbCrLf & vbCrLf

    End If
    If emptyCount > 0 Then

        report = report & "【注意】空白セルが " & emptyCount & " 件あります。" & vbCrLf

        report = report & "　→ 検索範囲に空白があると予期しない結果の原因になります。" & vbCrLf & vbCrLf

    End If
    If numCount > 0 And strCount > 0 Then

        report = report & "【警告】数値と文字列が混在しています。" & vbCrLf

        report = report & "　→ データ型を統一してください。" & vbCrLf & vbCrLf

    End If
    If isSorted Then

        report = report & "【OK】数値データは昇順に並んでいます。" & vbCrLf

    Else

        report = report & "【警告】数値データが昇順に並んでいません。" & vbCrLf

        report = report & "　→ VLOOKUPのTRUE検索やMATCHの1/-1検索が誤動作します。" & vbCrLf

    End If

MsgBox report, vbInformation, "診断結果" End Sub

このマクロは、近似値検索がうまくいかないと相談を受けたとき、まず最初に走らせるものです。データ型の混在、文字列数値の存在、ソート状態の崩れ、空白やエラーの混入という4大トラブル原因を一発で洗い出せます。経験上、近似値検索のトラブルの90%以上はこの4つのどれかが原因です。

誰も教えてくれなかった実務トラブルと体験ベースの解決法

CSVインポートで先頭のゼロが消えて近似値検索が全滅する

これは本当によくある話です。基幹システムから出力したCSVファイルに「001」「002」のようなゼロ埋めのコード番号が入っていて、それをExcelで開くと自動的に数値の1、2に変換されてしまいます。検索元のデータでは文字列として「001」を検索しているのに、検索先では数値の1になっている。当然ヒットしません。

解決策は、CSVをExcelで直接開かず、データタブの「テキストまたはCSVから」機能（Power Query）でインポートすることです。Power Queryならインポート時に各列のデータ型を明示的に指定できるので、コード番号を文字列のまま取り込めます。Excel 2016以降であれば標準搭載されているので、CSVの直接ダブルクリックオープンは今すぐやめて、必ずPower Query経由でインポートする運用に切り替えてください。

別の担当者がソートを壊して近似値検索が静かに狂い続ける

自分が完璧にソートして近似値検索を組んでも、翌週に別の担当者がデータを追記する際にソートを崩してしまうことがあります。しかもVLOOKUPの近似値検索はエラーを出さずにデタラメな値を返すため、何週間も気づかずに誤った数値で業務報告をしていた……という事故を私は何度も目にしてきました。

根本的な対処法は3つあります。1つ目は、マスターテーブルのシートをシート保護で編集不可にし、データ追加は管理者のみが行う運用にすること。2つ目は、VLOOKUPの近似値検索をやめてXLOOKUPの検索モード1（先頭から順に検索、ソート不要）に切り替えること。3つ目は、前述の昇順検証VBAマクロをブックのBeforeSaveイベントに仕込んで、保存するたびに自動チェックが走る仕組みにすることです。

以下に、保存時に自動でソート検証を実行するイベントマクロのサンプルを載せます。ThisWorkbookモジュールに記述してください。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    '保存時に料金表シートのA列が昇順か自動チェック

    '動作確認: Excel 2016 / 2019 / 2021 / Microsoft 365 (Windows)

    Dim ws As Worksheet

    Dim lastRow As Long

    Dim i As Long
    On Error Resume Next

    Set ws = ThisWorkbook.Worksheets("料金表")

    On Error GoTo 0
    If ws Is Nothing Then Exit Sub
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = 3 To lastRow If IsNumeric(ws.Cells(i, 1).Value) And IsNumeric(ws.Cells(i - 1, 1).Value) Then If ws.Cells(i, 1).Value < ws.Cells(i - 1, 1).Value Then MsgBox "【注意】料金表のA列が昇順に並んでいません（" & i & "行目付近）。" & vbCrLf & _ "VLOOKUP近似値検索が誤動作する可能性があります。" & vbCrLf & _ "ソートし直してから保存してください。", vbExclamation, "ソート検証" Exit Sub End If End If Next i End Sub

小数点の丸め誤差で近似値検索が一致しない

Excelの内部では浮動小数点演算が行われているため、表示上は「0.1」と見えていても実際には「0.10000000000000001」のような微細な誤差を含んでいることがあります。この誤差のせいで、完全一致検索はもちろん、近似値検索でも期待通りの結果にならないことがあります。

典型的なのは、計算結果のセル（たとえば=10/100で0.1を生成）と、直接入力したセル（0.1とタイプ）を近似値検索で照合するケースです。見た目はどちらも0.1ですが、内部的に微妙に値が異なるため一致しないのです。

対策としては、検索値の側でROUND関数を使って明示的に丸めるのが確実です。=XLOOKUP(ROUND(計算結果, 10), 検索範囲, 戻り範囲, , -1)のように、十分な桁数で丸めておけば、浮動小数点誤差を事実上無視できます。VLOOKUPの近似値検索でも同様に、検索値をROUNDで丸めてから渡すのが安全策です。

VLOOKUP近似値検索で#N/Aではなく間違った値が返るのが一番怖い

正直に言うと、これが近似値検索で最も危険なトラブルです。完全一致検索なら値が見つからなければ#N/Aエラーが出るので問題に気づけます。しかし近似値検索では「何かしらの値」が必ず返ってくるため、それが正しいのか間違っているのか、パッと見では判断できません。

私が情シスとして本当に何度も経験したのは、昇順ソートが一部崩れた状態のマスターテーブルで、VLOOKUPの近似値検索が「もっともらしいけれど実は間違っている値」を返し続け、月次の集計が何ヶ月もズレていたという事故です。発覚したときには過去の報告書をすべて修正するはめになり、関係者全員が地獄を見ました。

この経験から学んだ教訓は明確です。近似値検索の結果を業務で使う場合は、必ず検算の仕組みを入れること。具体的には、近似値検索で返ってきた値と検索値の差が許容範囲内かどうかをIF関数で確認する列を隣に追加しておくのです。たとえば差が検索値の10%を超えたら「要確認」と表示するような数式を仕込んでおけば、異常値を早期に検知できます。

知っておくと差がつく近似値検索の裏テクニック

XLOOKUP＋SORT関数で元データを壊さずに近似値検索する

Microsoft 365のダイナミック配列に対応した環境なら、SORT関数とXLOOKUPを組み合わせることで、元データをソートせずに昇順ソート済みの仮想配列を作って近似値検索するという離れ業が可能です。

=XLOOKUP(検索値, SORT(検索範囲), SORTBY(戻り範囲, 検索範囲), , -1)

この数式はSORTで検索範囲を昇順にソートした仮想配列を作り、SORTBYで戻り範囲も同じ順序で並び替えた仮想配列を作って、その上でXLOOKUPの近似値検索を実行します。元のシートのデータ順は一切変わりません。ただし、XLOOKUPの検索モード1（デフォルト）ならソート不要で近似値検索ができるので、この手法が必要になるのは検索モード2（バイナリサーチ）で高速化を図りたい大量データの場合に限られます。

条件付き書式で近似値の判定結果を視覚化する

近似値検索の結果が正しいかどうかを視覚的に確認する方法として、条件付き書式の活用がおすすめです。先ほど触れた「検算の仕組み」を、セルの色で表現するイメージです。

たとえば、近似値検索で返った料金区分と実際の注文重量の差が大きい場合にセルを赤く着色するには、対象セル範囲に条件付き書式で数式ルールを設定します。=ABS(検索値-XLOOKUP結果)>許容値のような数式を条件に指定すれば、異常なマッチングが発生したセルだけが赤く浮かび上がります。月次の締め処理前にこのチェックを一目で確認できるようにしておくと、前述の「静かに狂い続ける」事故を確実に防げます。

名前付き範囲を使って近似値検索の保守性を高める

数式の中にセル範囲（$A$2:$A$100など）を直接書いていると、データが増えたときに範囲を手動で拡張しなければなりません。これを忘れると新しいデータが検索対象外になり、近似値検索の結果が不正確になります。

テーブル化が最善策ですが、さまざまな理由でテーブル化できない場合は、名前付き範囲をOFFSET＋COUNTA関数で動的に定義する方法があります。「数式」タブの「名前の管理」から、参照範囲に=OFFSET(シート名!$A$2, 0, 0, COUNTA(シート名!$A:$A)-1, 1)と設定すれば、データの増減に自動追従する動的な名前付き範囲が作れます。この名前をVLOOKUPやXLOOKUPの引数に使えば、保守の手間が激減します。

ぶっちゃけこうした方がいい！

ここまで近似値検索について、関数の使い分けから現場のトラブル事例、VBAマクロまで相当な量を書いてきましたが、情シスを12年やってきた人間としてぶっちゃけた本音を言わせてください。

近似値検索は、可能な限りXLOOKUP一択に統一してしまうのが、ぶっちゃけ一番楽だし効率的です。

VLOOKUPの近似値検索は確かに歴史が長くて情報も多いですが、昇順ソート必須、デフォルトが近似値検索、左端列限定、エラー処理にIFERROR必須と、地雷が多すぎます。現場で起きるトラブルの大半は、これらの制約を知らないか忘れているかのどちらかが原因です。XLOOKUPに乗り換えるだけで、これらの問題がまとめて消えます。昇順ソート不要、デフォルト完全一致、左右どちら方向でも検索可能、エラー処理は引数で完結。同じ近似値検索をするなら、わざわざ罠が多いVLOOKUPを使い続ける理由がないのです。

「でもチーム内にExcel 2019の人がいるから……」という声もよく聞きます。それなら、そのファイルだけINDEX＋MATCHで書いて、自分の作業用ファイルはXLOOKUPで統一すればいい。互換性のためにすべての数式を古い書き方に合わせるのは、言ってしまえば「一番遅い人に合わせてチーム全員が走る」ようなものです。自分の生産性を犠牲にする必要はありません。

そしてもう一つ。近似値検索を使う場面では、「その近似値検索、本当に近似値である必要があるのか？」を一度立ち止まって考えてほしいのです。体感ですが、現場で近似値検索を使っているケースの3割くらいは、実はマスターテーブルの設計を見直せば完全一致検索で済む話だったりします。料金区分のコードを振っておくとか、ROUNDDOWN関数で区分値に丸めてから完全一致で検索するとか、ちょっとした前処理を入れるだけで、近似値検索特有のリスクをゼロにできるケースは思った以上に多いのです。

関数の使い方を覚えることも大事ですが、本当に実務で差がつくのは「そもそもこの処理に近似値検索が必要なのか」というデータ設計の視点です。近似値検索を使わなくて済むデータの作り方を知っている人は、トラブルも少ないし修正も早い。結局のところ、最強の近似値検索テクニックは「近似値検索を使わなくて済む仕組みを作ること」だと、12年間の現場経験を通じて強く実感しています。

Excelの近似値検索に関するよくある疑問を解決

VLOOKUPの近似値検索で結果がおかしいのはなぜ？

もっとも多い原因は、検索範囲が昇順にソートされていないことです。VLOOKUPの近似値検索（TRUE）は内部でバイナリサーチを使うため、データが昇順に並んでいないと見当違いの結果を返します。しかもエラーにならないので、間違いに気づきにくいのが厄介です。まずは検索範囲のソート状態を確認してください。もう一つの原因として、第4引数を書き忘れて意図せず近似値検索になっているケースもあります。完全一致で検索したいなら、必ずFALSEを明示しましょう。

XLOOKUPはどのバージョンのExcelで使える？

Microsoft 365とExcel 2021以降で使えます。Excel 2016やExcel 2019では残念ながら利用できません。ただし、Microsoft 365のサブスクリプションでXLOOKUPを使って作成したファイルを、Excel 2019で開いた場合には数式がそのまま残りますが、再計算されないため値が更新されない点に注意が必要です。チーム内で旧バージョンのExcelを使っているメンバーがいる場合は、INDEX＋MATCH関数を代替として使う方が安全です。

近似値で「上にも下にも一番近い値」を探すにはどうすればいい？

VLOOKUPやXLOOKUPの標準的な近似値検索は「以上」か「以下」の一方向にしか対応していません。上下の方向に関係なく絶対値で最も近い値を見つけるには、ABS関数とMIN関数を組み合わせた配列数式を使います。具体的には=INDEX(範囲, MATCH(MIN(ABS(範囲-基準値)), ABS(範囲-基準値), 0))です。Microsoft 365環境であれば、LET関数と組み合わせて=LET(X, ABS(範囲-基準値), XLOOKUP(MIN(X), X, 範囲))とも書けます。

VLOOKUP関数のTRUEがなぜ近似値でFALSEが完全一致なの？

これは多くの人が直感に反すると感じるポイントです。命名の由来は「近似値を含めるかどうか」という論理に基づいています。TRUE（真）は「近似値を含めて検索する＝はい」、FALSE（偽）は「近似値を含めず厳密に一致するものだけ＝いいえ」という意味です。XLOOKUPではこの分かりにくい仕様が改善され、一致モードを0、-1、1、2といった数値で指定する方式に変わりました。デフォルトが完全一致（0）になったのも、実際の利用場面にマッチした合理的な改善です。

近似値検索は文字列データにも使えるの？

技術的にはVLOOKUPやMATCH関数で文字列に対して近似値検索を実行できますが、実用的にはおすすめしません。文字列の近似値検索では前方一致のような動作になり、結果を予測しにくいからです。文字列の類似検索が必要な場合は、ワイルドカード検索（XLOOKUPの一致モード2）や、サードパーティのアドインで提供されているファジーマッチ機能を検討するのが現実的です。2025年には、XLOOKUPのようなインターフェースでテキストの類似度を計算してくれるFUZZY.LOOKUPというアドインも登場しています。

今すぐパソコンやスマホの悩みを解決したい！どうしたらいい？

いま、あなたを悩ませているITの問題を解決します！
「エラーメッセージ、フリーズ、接続不良...もうイライラしない！」

あなたはこんな経験はありませんか？

✅ ExcelやWordの使い方がわからない💦
✅ 仕事の締め切り直前にパソコンがフリーズ💦
✅ 家族との大切な写真が突然見られなくなった💦
✅ オンライン会議に参加できずに焦った💦
✅ スマホの重くて重要な連絡ができなかった💦

平均的な人は、こうしたパソコンやスマホ関連の問題で年間73時間（約9日分の働く時間！）を無駄にしています。あなたの大切な時間が今この悩んでいる瞬間も失われています。

LINEでメッセージを送れば即時解決！

すでに多くの方が私の公式LINEからお悩みを解決しています。

最新のAIを使った自動応答機能を活用していますので、24時間いつでも即返信いたします。

誰でも無料で使えますので、安心して使えます。

問題は先のばしにするほど深刻化します。

小さなエラーがデータ消失や重大なシステム障害につながることも。解決できずに大切な機会を逃すリスクは、あなたが思う以上に高いのです。

あなたが今困っていて、すぐにでも解決したいのであれば下のボタンをクリックして、LINEからあなたのお困りごとを送って下さい。

相談しに行く

ぜひ、あなたの悩みを私に解決させてください。

まとめ

Excelの近似値検索は、料金表の判定から在庫管理、データマッチングまで、ビジネスのあらゆる場面で活躍するスキルです。この記事で解説した内容を振り返っておきましょう。

VLOOKUPの近似値検索は「検索値以下の最大値」を返す基本機能ですが、昇順ソートが必須という制約があります。XLOOKUPなら昇順ソート不要で、一致モードの指定ひとつで「以下の最大値」も「以上の最小値」も自在に切り替えられます。INDEX＋MATCH関数はXLOOKUPが使えない環境での強力な代替手段であり、大量データの高速検索にも威力を発揮します。そして、方向を問わず絶対値で一番近い値を見つけるには、ABSとMINを組み合わせた配列数式が決め手になります。

2026年現在、Copilotの進化によって「どの関数を使えばいいか」を自分で調べる必要すらなくなりつつあります。ただし、数式の意味を理解していなければ、AIが提案した数式の正しさを判断できません。まずはこの記事で紹介した7つのテクニックを手を動かして試し、近似値検索の仕組みを体で覚えてください。そのうえでCopilotを活用すれば、Excelでの業務効率は飛躍的に向上するはずです。