XLOOKUPで「70以上80未満」のような範囲を検索するには、下限値の表を用意して第5引数の一致モードを -1 にします。入力値以下で最も近い下限が選ばれるため、得点別の評価や金額別のランクを求められます。
「以上・未満」の区切りを表にする
得点をB2、下限値をD3:D6、評価をF3:F6に入力します。
| 下限値 | 適用する得点 | 評価 |
|---|---|---|
| 0 | 0以上60未満 | F |
| 60 | 60以上70未満 | C |
| 70 | 70以上80未満 | B |
| 80 | 80以上 | A |
評価を表示するB3に次の式を入力します。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUP の基本（書き方・一致モード・下から検索・VLOOKUP との違い）は、XLOOKUP関数の使い方にまとめています。
境界値で動きを確認する
| B2の得点 | 結果 |
|---|---|
| -1 | 対象外 |
| 0 | F |
| 59 | F |
| 60 | C |
| 69 | C |
| 70 | B |
| 79 | B |
| 80 | A |
| 101 | A |
一致モード -1 と 1 の違い
| 一致モード | 一致しないときに選ぶ値 | 表に入れる境界 |
|---|---|---|
| -1 | 入力値より小さい側で最も近い値 | 区分の下限 |
| 1 | 入力値より大きい側で最も近い値 | 区分の上限 |
例えば送料を「1kg以下」「1kg超2kg以下」「2kg超5kg以下」に分けるなら、上限1・2・5と送料を対応させて一致モード1を使えます。検索範囲をH3:H5、送料をI3:I5、重量をE2に置く場合です。
並べ替えが必要なのはどの指定か
VLOOKUPで求める場合
D3:F6の先頭列Dが下限、3列目Fが評価なら、次の式でも求められます。
【実機で検証】エラーが出ないまま違う区分になる場面
下限が 0・60・70・80 の表で、入力が文字や空のときと、計算結果を探すときの動きを Excel の実機で試しました。
| 入力 | XLOOKUP（-1） | XLOOKUP（1） | VLOOKUP（TRUE） |
|---|---|---|---|
| 文字の「70」 | A | なし | #N/A |
| 文字の「欠席」 | A | なし | #N/A |
| 空のセル | A | なし | D |
一致モード -1 では、文字や空のセルでもエラーにならず、最上位の A が返りました。入力チェックが必要なのはこのためです。
計算結果は境界でずれることがある
1-0.9 の結果を 0・0.1・0.2 の表で探すと、0.1 の区分ではなく 0 の区分が返りました。=(1-0.9)=0.1 は TRUE なのに、検索では 0.1 より小さい値として扱われます。ROUND(1-0.9,1) で丸めてから探すと、0.1 の区分が返りました。計算結果で検索するときは、丸めておきます。
並びが崩れた表で比べる
| 点数 | XLOOKUP（-1） | XLOOKUP（-1・二分探索） | VLOOKUP（TRUE） |
|---|---|---|---|
| 65 | C（正しい） | D | D |
| 75 | B（正しい） | D | D |
| 85 | A（正しい） | C | C |
下限の列を 70・0・80・60 の順にした表です。第6引数を省略した XLOOKUP は正しい区分を返し、二分探索と VLOOKUP の TRUE は違う区分をエラーなしで返しました。
XLOOKUPの範囲検索がうまくいかない・エラーになるときの確認表
| 症状 | 主な原因 | 確認と対処 |
|---|---|---|
| 評価が1つずれる | 下限の表を一致モード 1 にしている／「以上」と「未満」の区切りが逆 | 下限値の表は -1、上限値の表は 1 にする。境界値（60点・70点など）で確かめる |
| 空欄や文字なのに、最後の評価（A）が出る | 入力が数値ではない | IFで、未入力と数値以外を先に止める |
| 100点を超えてもAになる | XLOOKUPには上限の条件が無い | 入力規則や IF で、0～100に制限する |
| 「対象外」が出る | 表の最小の下限より小さい値を入力している | 下限の最小値を 0 にして、表を確認する |
| #NAME? になる | XLOOKUPが使えないExcel（2019・2016）で開いている | VLOOKUP（TRUE）の近似一致を使う |
日付の期間検索にも使える
仕様の確認：Microsoft公式・XLOOKUP関数
コメント