XLOOKUPの近似値検索で多重IFをマスター化する

境界値を数式から分離し、業務ルールを表として管理する

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。

記事要約

点数、金額、数量、日付などを段階分けする処理は、多重IFで書くと条件が数式の中へ埋め込まれます。条件変更のたびに数式を直す必要があり、境界値の重複や修正漏れも見つけにくくなります。

XLOOKUPの一致モード -1を使うと、完全一致がない場合に「次に小さい値」を採用できます。「○○以上」という下限値をマスター表に並べれば、判定処理をXLOOKUP、業務ルールをマスターへ分離できます。

重要なのは、式を短くすることではありません。境界値の意味、データ型、最小値、重複、エラー時の動作、境界値テストまでをマスターの設計として管理することです。

本記事の対象とゴール

この記事は、多重IFでランク、単価、料率、送料、適用日などを判定しているExcel利用者を対象にしています。

ゴールは次のとおりです。

XLOOKUPの近似値検索を下限値マスターへ適用できる
一致モードと検索モードを混同しない
多重IFから境界値を安全に移行できる
マスター設定漏れと境界値の誤りを検査できる

1. 多重IFには処理とルールが混在する

売上金額に応じてランクを返す式を考えます。

=IF(B2>=500000,"A",

 IF(B2>=300000,"B",

 IF(B2>=100000,"C","D")))

この式には、二種類の情報が入っています。

B2を判定するという処理
100,000、300,000、500,000という業務ルール

条件が増えるほど、数式は業務ルールの保管場所になります。ルールを確認するために数式を読まなければならず、同じ式が複数列や複数ブックへ複製されると、どれが正本か分からなくなります。

2. 下限値マスターへ分離する

条件を「この値以上なら、この結果」として整理します。

下限金額  顧客ランク

0         D

100000    C

300000    B

500000    A

検索値が250,000なら、250,000以下で最大の下限値は100,000です。その行のCを返します。

式は次の形になります。

=XLOOKUP(B2,$H$2:$H$5,$I$2:$I$5,"判定不能",-1)

第五引数の -1は、完全一致がなければ次に小さい項目を返す指定です。

3. 一致モードと検索モードを分ける

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

=XLOOKUP(

    検索値,

    検索範囲,

    戻り範囲,

    見つからない場合,

    一致モード,

    検索モード

近似値検索で使う一致モードは、次の四つです。

0: 完全一致。既定値
-1: 完全一致。なければ次に小さい項目
1: 完全一致。なければ次に大きい項目
2: ワイルドカード一致

検索モードは、どの向き・方式で探すかを指定する別の引数です。2と-2の二分探索を使う場合は、検索配列が指定された順序に並んでいなければ不正な結果になります。

通常の先頭から検索するモードでは、近似一致のためだけに並べ替えが必須とは限りません。それでも、下限値マスターは昇順に統一するのが安全です。人が境界値を確認しやすく、重複や逆転を検査しやすくなるためです。

4. Excelテーブルで列の意味を表す

マスターをExcelテーブル tbl顧客ランクにすると、式を列名で書けます。

=XLOOKUP(

    [@売上金額],

    tbl顧客ランク[下限金額],

    tbl顧客ランク[顧客ランク],

    "判定不能",

-1

テーブル化には次の利点があります。

行追加時に参照範囲が拡張される
数式から列の意味を読める
セル番地のずれを防ぎやすい
重複や空白行を表として検査できる
業務担当者が境界値を確認しやすい

列名は「値」「結果」ではなく、「売上下限」「顧客ランク」のように意味を明示します。

5. 下限値方式と上限値方式

「○○以上」を表にする下限値方式では、一致モード -1を使います。

点数下限  評価

0         D

60        C

70        B

80        A

90        S

85点なら、次に小さい80の行からAを返します。

「○○以下」を表にする上限値方式では、一致モード 1を使います。

点数上限  評価

59        D

69        C

79        B

89        A

100       S

85点なら、次に大きい89の行からAを返します。

一つのマスター内で下限と上限の意味を混在させてはいけません。境界値ちょうどの結果がどちらになるか、列名と説明で統一します。

6. 日付や料率にも使える

適用開始日は、日付の下限値として扱えます。

適用開始日  単価

2025/04/01  1000

2025/10/01  1100

2026/04/01  1200

=XLOOKUP(

B2,

    tbl単価[適用開始日],

    tbl単価[単価],

    NA(),

-1

B2が2026年1月10日なら、直前の適用開始日である2025年10月1日の単価1100を返します。

同じ考え方は、数量別単価、重量別送料、売上別料率、勤続年数別手当など、一つの連続値を段階へ分ける処理に使えます。

7. マスター設計の必須ルール

7.1 最小値を用意する

最初の下限値より小さい検索値には、次に小さい項目がありません。0未満を許可しないなら入力チェックを行い、負数も扱うなら負数用の行またはエラー規則を決めます。

7.2 下限値を重複させない

同じ下限値に異なる結果があると、どちらが正しい業務ルールか判断できません。下限値は原則として一意にします。

7.3 型をそろえる

検索値が数値で、マスターが文字列の "100000"では、見た目が同じでも正しい比較になりません。数値、日付、時刻、文字列の型をそろえます。

7.4 空白で異常を隠さない

=IFERROR(XLOOKUP(...),"")

この式は、設定漏れだけでなく、型不一致や式の誤りも空白へ変えます。計算で使う結果なら NA()、人が確認する表なら「マスター未設定」など、異常を区別できる戻り値を検討します。

7.5 ルール変更者と承認方法を決める

マスターへ外出しすると、数式を直さず業務ルールを変えられます。これは利点であると同時に、変更管理が必要になるという意味です。更新者、適用日、承認者、変更履歴を業務の重要度に応じて管理します。

8. 多重IFから移行する手順

手順1　IFの条件を評価順に書き出す。

手順2　「○○以上」か「○○以下」かを統一する。

手順3　境界値と結果を二列のマスターへする。

手順4　下限値方式なら昇順に並べる。

手順5　XLOOKUPの一致モードを選ぶ。

手順6　見つからない場合の戻り値を決める。

手順7　元のIFと新しいXLOOKUPを並べて比較する。

手順8　合格後に数式を置き換える。

元のIFを先に消さず、同じ入力に対する結果を比較できる期間を設けると安全です。

9. 境界値テストを必ず行う

下限が0、100,000、300,000、500,000なら、少なくとも次を試します。

空白

文字列

負数

エラー値

各境界の一つ前、境界値そのもの、一つ後を見ると、不等号の向きや上限・下限の取り違えを発見しやすくなります。

10. 単純な近似検索に向かない条件

単純なXLOOKUP近似検索が得意なのは、一つの連続値を境界で段階分けする処理です。

次のような条件は、XLOOKUP一つへ押し込まない方がよい場合があります。

部署と契約区分の組み合わせ
複数フラグのAND・OR
例外条件が優先される判定
上限と下限の両方を持つ区間
地域別に異なる境界値
条件の優先順位が業務ルールになっている処理

複合キー、FILTER、決定表、Power Query、VBAなどを検討します。式を短くすることより、ルールの意味を検証できることを優先します。

11. まとめ

XLOOKUPの近似値検索を使うと、多重IFに埋め込まれた境界値をマスター表へ分離できます。

下限値方式の基本形は次です。

=XLOOKUP(

    判定対象,

    tblマスター[下限値],

    tblマスター[判定結果],

    "未設定",

-1

ただし、マスター化しただけでは安全になりません。境界値の意味、最小値、重複、型、エラー時の戻り値、変更管理、境界値テストを合わせて設計する必要があります。

この方法の本質は、判定の仕組みをXLOOKUPへ、業務上の境界値をマスターへ分けることです。分離した結果、数式を読む人と業務ルールを決める人が、同じ表を見て確認できるようになります。

出典メモ

記事素材: ほえほえ作成の xlookup_approximate_match_master.md。2026-08-01確認。
Microsoft Support「XLOOKUP function」: https://support.microsoft.com/en-us/excel/functions/xlookup-function
一致モード -1、1と、検索モード 2、-2の並べ替え要件はMicrosoftの公式説明と照合した。
記事内のマスター名、金額、日付、テスト値は説明用の例である。