XLOOKUP関数 ● さまざまな条件を指定してデータを取り出す (2)近似一致検索の場合

［Excel 2021以降］

Excel 2021の新関数である XLOOKUP（エックス・ルックアップ）関数で、近似一致検索の表引きを行う方法を紹介します。

XLOOKUP関数の基本的な使い方や完全一致検索については、下記のページを参照してください。

XLOOKUP関数 ● さまざまな条件を指定してデータを取り出す (1)完全一致検索の場合

1 書式
2 使用例1…「○点以上は評価X」形式の表から成績を求める
3 検索範囲は昇順でなくてもOK、左端でなくてもOK
4 使用例2…「△点までは評価X」形式の表から成績を求める
5 ［検索値］に想定外の値を指定した場合の挙動

書式

さまざまな条件を指定してデータを取り出す
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)

［検索範囲］から［検索値］を探し、見つかった場合は［戻り値範囲］の該当する位置から値を返します。見つからなかった場合は、［見つからない場合］を返します。［一致モード］で一致の判断基準、［検索モード］で検索の方向を指定できます。

■引数

引数	説明
検索値	検索する値を指定する。全角文字と半角文字、アルファベットの大文字と小文字は区別されず同じ文字と見なされる。
検索範囲	［検索値］を検索する。1列または 1行の範囲を指定する。
戻り値範囲	値を取り出す範囲を指定する。［検索範囲］が 1列の場合は同じ行数、1行の場合は同じ列数の範囲を指定すること。
見つからない場合	［検索範囲］の中に［検索値］が見つからない場合に返す値を指定する。省略した場合は、エラー値「#N/A」が返される。
一致モード	検索の際に「一致」と判断する基準を表A の数値で指定する。省略した場合は、完全一致で検索される。
検索モード	検索する方向を表B の数値で指定する。省略した場合は、先頭から末尾に向かって検索される。

■表A　引数［一致モード］の設定値

設定値	説明
0 または省略	完全一致
-1	完全一致または次に小さい項目
1	完全一致または次に大きい項目
2	ワイルドカード文字との一致

■表B　引数［検索モード］の設定値

設定値	説明
1 または省略	先頭から末尾へ検索
-1	末尾から先頭へ検索
2	バイナリ検索（［検索範囲］が昇順の場合）
-2	バイナリ検索（［検索範囲］が降順の場合）

使用例1…「○点以上は評価X」形式の表から成績を求める

XLOOKUP関数の5番目の引数［一致モード］に「-1」を指定すると、「○以上△未満」の範囲から表引きを行えます。表引き用の表には、「○以上」にあたるデータを並べた列を用意します。

図1では、得点に対応する評価を評価判定表から表引きしています。評価判定表には「0 ～ 59」「60 ～ 69」「70 ～ 79」……と、得点の範囲が入力されていますが、XLOOKUP関数で使用するのは「○以上」にあたる「0」「60」「70」……の列だけです。「～ 59」などの列は使用しないので、入力しなくてもかまいません。

先頭のセルC3の場合、［検索値］に「100」点のセルB3、［検索範囲］に「○以上」が入力されたセルE3～E7、［戻り値範囲］に評価が入力されたセルH3～H7を指定します。数式をコピーしたときにずれないように、［検索範囲］と［戻り値範囲］は絶対参照で指定します。あとは、［見つからない場合］に「"---"」を指定し、［一致モード］に「-1」を指定すればOKです。

=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)

セルC3 | =XLOOKUP(B3,$E$3:$E$7,$H$3:$H$7,"---",-1)

図1　セルC3にXLOOKUP関数を入力して、セルC9までコピーする。得点に対応する評価が表示される。

ちなみに、図1では［検索範囲］が昇順（小さい順）に並べられていますが、XLOOKUP関数では昇順でない表でも表引きできます。詳しくは、次項で解説します。

メモ

VLOOKUP関数でも同様の評価を求められる

VLOOKUP関数では、4番目の引数で「TRUE」を指定すると、XLOOKUP関数の［一致モード］に「-1」を指定したときと同じく「○以上」を並べた表から表引きを行えます。図1と同様の表引きを行うには、次のように式を立てます。

=VLOOKUP(B3,$E$3:$H$7,4,TRUE)

なお、XLOOKUP関数では［検索範囲］の並び順は昇順でなくてもかまいませんが、VLOOKUP関数の場合は昇順にしておく必要があります。

メモ

実際には「0以上60未満」の範囲で検索される

図1 の評価判定表には「0以上59以下」「60以上69以下」……「90以上100以下」の形式で得点が入力されていますが、［検索範囲］に指定したのは「○以上」にあたる「0」「60」「70」「80」「90」のみです。［一致モード］に「-1」を指定したときに実際に検索されるのは、「0以上60未満」「60以上70未満」「70以上80未満」「80以上90未満」「90以上」の範囲となります。

［検索値］に指定する得点は「0以上100以下」の整数を想定していますが、「100」より大きい数値を指定した場合の結果は「90以上」の場合と同じ「A」となります。また、小数の「59.5」を指定した場合の結果は「F」となります。

検索範囲は昇順でなくてもOK、左端でなくてもOK

繰り返しになりますが、XLOOKUP関数では［検索範囲］が昇順に並んでいなくてもかまいません。降順でもOKですし、バラバラの順序でもOKです。図2では、降順に並べ替えた評価判定表から表引きしています。数式も結果も図1 と同じです。

図2　セルC3～C9の数式はそのまま、評価判定表を得点の降順に並べ替えてみた。数式の結果は図1 と同じ。

また、XLOOKUP関数では［検索範囲］が［戻り値範囲］の左右どちらにあってもかまいません。図3では、得点欄と評価欄を入れ替えた評価判定表から表引きしています。結果は図1 と同じです。

セルC3 | =XLOOKUP(B3,$F$3:$F$7,$E$3:$E$7,"---",-1)

図3　評価判定表の得点のセルと評価のセルを入れ替えると、XLOOKUP関数の引数も自動的に入れ替わる。数式の結果は図1 と同じ。

ちなみに、VLOOKUP関数では図2 や図3 のような表から表引きできません。

メモ

高速検索する場合は並べ替えが必須

XLOOKUP関数でバイナリ検索（高速の検索）をしたい場合は、［検索範囲］の並べ替えが必須です。昇順に並べ替えた場合は6番目の引数［検索モード］に「2」を指定し、降順に並べ替えた場合は「-2」を指定すると、バイナリ検索が行われます。

使用例2…「△点までは評価X」形式の表から成績を求める

引数［一致モード］に「1」を指定すると、「○超△以下」の範囲から表引きを行えます。表引き用の表には、「△以下」にあたるデータを並べた列を用意します。

図4では、［一致モード］に「1」を指定した表引きを行っています。［検索範囲］に指定するのは「△以下」にあたる数値が入力されたセルG3～G7です。「90 ～」などの列は使用しないので、入力しなくてもかまいません。

=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)

セルC3 | =XLOOKUP(B3,$G$3:$G$7,$H$3:$H$7,"---",1)

図4　図2 と同じ表から表引きを行った。図2 の数式との違いは［検索範囲］と［一致モード］のみ。結果は図2 と同じ。

メモ

実際には「○超△未満」の範囲で検索される

図4の評価判定表には下から順に「0以上59以下」「60以上69以下」……「90以上100以下」の形式で得点が入力されていますが、［検索範囲］に指定したのは「△以下」にあたる「59」「69」「79」「89」「100」のみです。［一致モード］に「1」を指定したときに実際に検索されるのは、「59以下」「59超69以下」「69超79以下」「79超89以下」「89超100以下」の範囲となります。

［検索値］に指定する得点は「0以上100以下」の整数を想定していますが、「0」より小さい数値を指定した場合の結果は「59以下」の場合と同じ「F」となります。また、小数の「89.5」を指定した場合の結果は「A」となります。