はじめに：VLOOKUPで"近い値"が探せなくて困ったことはありませんか？
VLOOKUPの「完全一致」では対応できなくて、困った経験はないでしょうか？
たとえばこんな場面です：
- 「予算100万円に最も近い商品を自動で探したい」
- 「目標値50に対して±10以内の実績を一覧にしたい」
- 「在庫数がバッファ範囲内に収まっている倉庫を絞り込みたい」
VLOOKUPの近似一致は「昇順ソート済みリストを上から検索する」仕組みであり、「差が最も小さい値」を探すには対応していません。こうした「近似範囲検索」は、ABS関数を起点にした発想転換で驚くほどシンプルに実現できます。
この記事では、「最も近い値を1件取得」と「範囲内の値を複数取得」の2パターンに分けて、実践的な数式を丁寧に解説します。
まず押さえる：「差の絶対値」で近さを数値化する
すべての数式に共通するのが、この考え方です：
=ABS(対象値 - 検索値)
ABS関数は正負を取り除き、「差の大きさだけ」を返します。ABS(95-100)=5、ABS(102-100)=2 のように、値が小さいほど近いとみなせます。
この差の絶対値を「最小化する（1件取得）」か「しきい値以内かで絞り込む（複数取得）」かが、今回の2パターンの分岐点です。
パターンA：最も近い値を1件取得する
① XLOOKUP＋ABS（Excel 365 / 2021）
サンプルデータ（A・B列）：
| A列（価格） | B列（商品名） |
|---|---|
| 95,000 | 商品A |
| 102,000 | 商品B |
| 110,000 | 商品C |
| 87,000 | 商品D |
目標：D1に入力した予算（例：100,000）に最も近い商品名を返す
=XLOOKUP(MIN(ABS(A2:A5-D1)), ABS(A2:A5-D1), B2:B5)
仕組み：
1. ABS(A2:A5-D1) → 各価格とD1の差の絶対値を配列で計算（例：{5000, 2000, 10000, 13000}）
2. MIN(...) → 最小値（2000）を取得
3. XLOOKUP → 配列の中から2000の位置を探し、対応する「商品B」を返す
⚠️ エッジケース：同じ差が2件ある場合（例：98,000と102,000は両方差2,000）、XLOOKUPはリストの先頭側（上にある行）を返します。意図的に制御したい場合は、追加条件での絞り込みが必要です。
② INDEX＋MATCH（Excel 2019以前・Mac版対応）
XLOOKUPが使えない環境では、INDEX＋MATCHが定番の代替手段です。
=INDEX(B2:B5, MATCH(MIN(ABS(A2:A5-D1)), ABS(A2:A5-D1), 0))
- Excel 365 / 2021：そのままEnterで確定
- Excel 2019以前：
Ctrl+Shift+Enterで配列数式として入力（数式が{ }で囲まれて確定）
MATCHの第3引数を0にするのは、「差の最小値と完全に一致する位置を探す」ためです。
XLOOKUPの「近似一致モード」との違い
XLOOKUPの第5引数（match_mode）に-1や1を指定する「近似一致」とは別物です。match_modeの近似一致はソート済みデータが前提で「以下／以上の最近値」を返すのに対し、今回のABS方式はソート不要で「差が最も小さい値」を返します。乱雑なリストに対応できるのが、ABS方式の強みです。
パターンB：±N以内の値を複数取得する
「最も近い1件」ではなく「±N以内のすべてを取り出したい」場合は、アプローチが変わります。
③ SUMPRODUCT＋ABS：件数をカウントする
目標：A列の中でD1（100,000）との差が10,000以内のものを数える
=SUMPRODUCT((ABS(A2:A5-D1)<=10000)*1)
COUNTIFは条件に数式を使えないため、SUMPRODUCTで代用します。
仕組み：
1. ABS(A2:A5-D1)<=10000 → 差が10,000以内なら TRUE、超えていれば FALSE の配列を生成
2. *1 → TRUE→1、FALSE→0 に数値変換
3. SUMPRODUCT → 1の個数を合計（＝条件に合う件数）
④ FILTER＋ABS：一覧表示する（Excel 365 / 2021）
目標：A列の中でD1との差が10,000以内の商品名をすべて表示する
=FILTER(B2:B5, ABS(A2:A5-D1)<=10000, "該当なし")
仕組み：
1. 第1引数 B2:B5：返したいデータ範囲（商品名）
2. 第2引数 ABS(A2:A5-D1)<=10000：絞り込み条件
3. 第3引数 "該当なし"：条件に合うものがゼロ件のときの表示
スピル（結果が複数行に自動展開される機能）により、条件に合うすべての商品名が縦に並んで表示されます。D1の値を変えるだけで結果が動的に更新されるため、条件付きリスト抽出に最適です。
実務応用：価格帯マッチングシート
データ構成：
| A列（仕入れ価格） | B列（商品名） |
|---|---|
| 980 | ノートA |
| 1,200 | ノートB |
| 1,450 | ノートC |
| 780 | メモ帳 |
E1に顧客の予算を入力して、最も近い商品を1件提案：
=XLOOKUP(MIN(ABS(A2:A5-E1)), ABS(A2:A5-E1), B2:B5)
予算±200円以内の候補を一覧表示：
=FILTER(B2:B5, ABS(A2:A5-E1)<=200, "予算に合う商品がありません")
E1の値を変えるだけで、最適商品と候補一覧が即座に更新されます。商品数が増えても数式の変更は不要です。
まとめ
| やりたいこと | おすすめの数式 | 対応バージョン |
|---|---|---|
| 最も近い値を1件取得 | XLOOKUP＋MIN＋ABS | 365 / 2021 |
| 旧バージョンで1件取得 | INDEX＋MATCH＋MIN＋ABS | 2010以降 |
| ±N以内の件数をカウント | SUMPRODUCT＋ABS | 2010以降 |
| ±N以内の値を一覧表示 | FILTER＋ABS | 365 / 2021のみ |
共通のポイントは「ABSで差を絶対値化してから検索・絞り込む」という発想です。この考え方を一度身につけると、「ぴったり一致」を超えた柔軟な検索が自在にできるようになります。
自分のシートのデータ範囲に置き換えて、ぜひ試してみてください！
