XLOOKUPで「以上・未満」を検索｜一致モード-1・1と境界値の考え方

XLOOKUPで「70以上80未満」のような範囲を検索するには、下限値の表を用意して第5引数の一致モードを -1 にします。入力値以下で最も近い下限が選ばれるため、得点別の評価や金額別のランクを求められます。

「以上・未満」の区切りを表にする

得点をB2、下限値をD3:D6、評価をF3:F6に入力します。

評価を表示するB3に次の式を入力します。

=XLOOKUP(B2,D3:D6,F3:F6,"対象外",-1)

72点なら下限70の行が選ばれ、評価はBです。72という値そのものが表にない場合も、近似一致で判定できます。XLOOKUPはMicrosoft 365・Excel 2024・2021で使用できます。

境界値で動きを確認する

この数式には上限100の条件がありません。101点も最後の区分であるAになります。0～100点だけを許可するなら、入力値を別に検査します。

=IF(B2="","",IF(NOT(ISNUMBER(B2)),"数値を入力",IF(OR(B2<0,B2>100),"0～100で入力",XLOOKUP(B2,D3:D6,F3:F6,"対象外",-1))))

点数を整数に限定する場合は、セルのデータの入力規則も整数0～100に設定します。文字列の「72」と数値の72が混ざらないようにしてください。

一致モード -1 と 1 の違い

例えば送料を「1kg以下」「1kg超2kg以下」「2kg超5kg以下」に分けるなら、上限1・2・5と送料を対応させて一致モード1を使えます。検索範囲をH3:H5、送料をI3:I5、重量をE2に置く場合です。

=XLOOKUP(E2,H3:H5,I3:I5,"対象外",1)

上限値だけの表では、0以下の重量も最初の区分に入ります。重量は正の数という入力条件を別途設定します。下限の表をそのまま一致モード1に変えても、意図した送料表にはなりません。

並べ替えが必要なのはどの指定か

XLOOKUPの通常の検索では、一致モード -1 や1を指定しただけで昇順・降順が必須になるわけではありません。ただし境界値の漏れや重複を確認しやすいよう、小さい順に管理するのがおすすめです。

第6引数の検索モードに2を指定するバイナリ検索は昇順、-2は降順が必要です。第5引数の「一致モード」と、第6引数の「検索モード」を混同しないでください。この記事の数式では第6引数を省略しています。

VLOOKUPで求める場合

D3:F6の先頭列Dが下限、3列目Fが評価なら、次の式でも求められます。

=IFNA(VLOOKUP(B2,D3:F6,3,TRUE),"対象外")

VLOOKUPのTRUEによる近似一致では、先頭列を昇順に並べる必要があります。Excel 2019・2016でも使える方法です。VLOOKUPにも範囲検索はできるので、既存の表の形とExcelのバージョンで選んでください。

日付の期間検索にも使える

下限値の代わりに料金の「適用開始日」を並べれば、指定日の料金を検索できます。終了日や休業期間がある場合の扱いは日付・期間のXLOOKUP検索で解説しています。未一致時の表示は空白・0・該当なしの使い分けも参照してください。

仕様の確認：Microsoft公式・XLOOKUP関数