「完全一致する値がない」——そのとき多くの人が遠回りをしている
Excelで値を検索するとき、一致するデータが見つからず手動で目視確認した経験はないでしょうか。実はその作業、適切な数式を1つ知っているだけで数秒で終わります。
現場でよくある場面を想像してください——
- 測定値に最も近い規格値を参照したい
- 入力した年齢に最も近いサンプルデータを引っ張りたい
- 売上実績に最も近い目標値を見つけたい
こういった「ぴったり一致はしないが、最も近い値を探したい」ケースに、XLOOKUP や INDEX+MATCH を使った最近値検索は非常に有効です。本記事では基本の考え方から実践的な数式まで、順を追って解説します。
まず理解したい「最も近い値」を探す考え方
最近値を探すには、「差の絶対値を計算して、その最小値を見つける」 というシンプルな発想が土台になります。
たとえば「目標値 75 に最も近い値」を探す場合：
| データ | 目標値との差 | 絶対値 |
|---|---|---|
| 60 | −15 | 15 |
| 72 | −3 | 3 |
| 80 | +5 | 5 |
| 91 | +16 | 16 |
| 55 | −20 | 20 |
絶対値が最も小さい「72（差の絶対値＝3）」が正解です。この考え方を ABS（絶対値）と MIN（最小値）で実装するのが基本パターンです。
準備：サンプルデータの配置
以下のセル配置を前提に解説します。実際にExcelで手を動かしながら読むと理解が深まります。
| セル | 内容 |
|---|---|
| A2:A6 | 60, 72, 80, 91, 55（数値データ） |
| B2:B6 | 商品A, 商品B, 商品C, 商品D, 商品E（対応する名称） |
| E1 | 75（検索したい値） |
方法①：XLOOKUP + ABS + MIN（Microsoft 365 / Excel 2021以降）
Microsoft 365 または Excel 2021 以降を使っているなら、これが最もシンプルな書き方です。
=XLOOKUP(MIN(ABS(A2:A6-E1)), ABS(A2:A6-E1), B2:B6)
数式の読み方
| パーツ | 動作 |
|---|---|
A2:A6 - E1 |
各データと検索値の差を配列で計算 → {-15, -3, 5, 16, -20} |
ABS(...) |
絶対値に変換 → {15, 3, 5, 16, 20} |
MIN(...) |
最小値を取得 → 3 |
XLOOKUP(3, {15,3,5,16,20}, B2:B6) |
絶対値が 3 の行の商品名を返す → 「商品B」 |
XLOOKUP は配列をそのまま扱えるため、Ctrl+Shift+Enter は不要です。読みやすくメンテナンスもしやすいのが大きな利点です。
応用：最も近い「値そのもの」を返したい場合
商品名ではなく数値データ（A列の値）を返したいときは、返す範囲を変えるだけです。
=XLOOKUP(MIN(ABS(A2:A6-E1)), ABS(A2:A6-E1), A2:A6)
方法②：INDEX + MATCH + ABS + MIN（Excel 2007以降・互換性重視）
古いバージョンのExcelや、他の環境との互換性が必要な場合は INDEX + MATCH の組み合わせを使います。
=INDEX(B2:B6, MATCH(MIN(ABS(A2:A6-E1)), ABS(A2:A6-E1), 0))
⚠️ Excel 2019以前を使用している場合： 配列数式として入力が必要です。数式を入力後、Ctrl + Shift + Enter で確定してください。数式バーに
{ }が表示されていれば正しく配列数式として認識されています。Microsoft 365 では通常の Enter で動作します。
ステップごとの動作
ABS(A2:A6 - E1)→ 絶対値の配列{15, 3, 5, 16, 20}を生成MIN({15, 3, 5, 16, 20})→ 最小値3を取得MATCH(3, {15, 3, 5, 16, 20}, 0)→3は2番目 →2を返すINDEX(B2:B6, 2)→ B列の2行目「商品B」を返す
よくあるエラーと対処法
#N/A エラーが出る
MATCHの第3引数が0（完全一致）になっているか確認- 検索範囲（A列）と返す範囲（B列）の行数が一致しているか確認
- データに空白セルや文字列が混在していないか確認（数値列に文字が入ると
ABSが機能しない）
意図しない値が返ってくる
- Excel 2019以前の場合、配列数式として確定できていない可能性があります。数式バーに
{}が表示されているか確認し、されていなければ Ctrl+Shift+Enter で再確定してください
同じ差の値が複数あるとき
差の絶対値が同じデータが複数存在する場合、データの上側（行番号が小さい方）が優先されて返されます。
特定の優先順位をつけたい場合は、AGGREGATE 関数や SMALL 関数と組み合わせることで対応できます。たとえば「2番目に近い値も取り出したい」場合は以下のように応用できます：
=INDEX(A2:A6, MATCH(AGGREGATE(15,6,ABS(A2:A6-E1),2), ABS(A2:A6-E1), 0))
AGGREGATE の第3引数 6 はエラー無視、第4引数 2 は「2番目に小さい値」を指定しています。
実務での活用シーン
| シーン | 検索値の例 | 検索対象の例 | 目的 |
|---|---|---|---|
| 品質管理 | 測定値（例：74.8mm） | 規格値一覧 | 最も近い規格との差異確認 |
| 人事評価 | 評価スコア（例：82点） | 等級テーブル | 対応する等級の自動判定 |
| 価格設定 | 原価（例：1,450円） | 価格帯テーブル | 適用する価格帯を自動参照 |
| データ分析 | 実績値 | ベンチマーク値 | 最も近いKPI基準との比較 |
たとえば品質管理の場面では、測定値 74.8 に対して規格値一覧（70, 75, 80, 85）から最も近い 75 を参照する、といった使い方が典型例です。数式の構造はまったく同じで、参照するセル範囲を置き換えるだけで応用できます。
まとめ：どの方法を使えばいいか
| 方法 | 対応バージョン | 特徴 |
|---|---|---|
| XLOOKUP + ABS + MIN | Microsoft 365 / Excel 2021〜 | 記述がシンプルで可読性が高い |
| INDEX + MATCH + ABS + MIN | Excel 2007〜 | 互換性が高く幅広い環境で動作する |
最近値検索の核心は「差の絶対値の最小値を見つける」 というただ一点です。ABS と MIN でその最小値を特定し、XLOOKUP または INDEX+MATCH で対応するデータを取り出す——この流れさえ理解すれば、どんな場面にも応用できます。
完全一致に縛られない柔軟なデータ活用を、ぜひ今日から取り入れてみてください。
この記事が役に立ったら、ブックマークやシェアをしていただけると嬉しいです。VLOOKUP・XLOOKUPの応用テクニックを扱った記事もあわせてご覧ください。
