Excel 検索値に一致するデータを検索する(XLOOKUP関数)

2020年10月19日

XLOOKUP関数は2020年1月にMicrosoft 365に追加された関数で

VLOOKUP関数HLOOKUP関数を統合し問題点を解消した関数です。

大幅に使い勝手が改善しているため今後はVLOOKUP関数HLOOKUP関数から

XLOOKUP関数に移行していくでしょう。

さらにINDEX関数とMATCH関数のメリットも取り込んでいるため

そちらからも移行していくでしょう。

仕様

=XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)
引数省略時の値説明
検索値省略不可検索する値を指定。
検索範囲省略不可検索値を探す範囲を指定。
戻り範囲省略不可検索結果として取得する範囲を指定。
見つからない場合#N/Aエラー検索に合致するセルが見つからない場合の結果を指定
一致モード0完全一致か曖昧検索かを指定。
・0:完全一致。
・-1:完全一致で見つからない場合は検索値より小さい中で最も近い値
・1:完全一致で見つからない場合は検索値より大きい中で最も近い値
・2:ワイルドカード(*?~)を指定する場合の指定
検索モード1検索方法を指定
・1:先頭から順に検索
・-1:末尾から順に検索
・2:検索範囲が昇順で並んでいる前提で二分探索
・-2:検索範囲が降順で並んでいる前提で二分探索

使用例

XLOOKUP関数を使用した場合に

同じ結果をVLOOKUP関数で出す場合と変化がある個所を赤字にしています。

XLOOKUPの使用例とVLOOKUPで同じ結果を出す場合の相違

最初の引数(検索値)の指定は変わりありません。

VLOOKUP関数から改善点

引数:検索範囲と戻り範囲

二つ目の引数ではこれまで表全体と取得する列(行)番号を指定していましたが、

XLOOKUP関数では検索する列と結果の列をセル範囲で指定するようになります。

ここの指定を縦方向にすればVLOOKUP関数に、横方向にすればHLOOKUP関数になります。

これにより検索範囲(例では魚)が左端や上端である必要性がなくなり

名前からコードを取得するような機能が簡単に実現可能になりました。

これまで検索範囲は左端である必要があったが、その制約が無くなった

よってこのような工夫をする必要がなくなりました。

更に取得項目を列番号を指定したため表の項目が追加された場合、

VLOOKUP関数の三番目の引数の数字をキー入力で変える必要がありましたが

列範囲になることで項目追加時に自動的に関数の数式が更新されるようになります。

絶対参照$で柔軟に固定することも可能です。

また結果の返却がセル範囲になるため、結果をSUM関数など他の関数に組み込んだり

XLOOKUP関数を入れ子にしてクロス検索が可能です。

更に検索結果に画像を使用することも可能になりました。

SUM関数に組み込む例

入力値によって対応する行(列)の合計を算出します。

XLOOKUP関数をSUM関数に組み込む例

従来ではOFFSET関数MATCH関数を組み合わせるか

事前に合計行を作成した上でのSUMIF関数が必要なですが、

XLOOKUP関数なら簡単に実装可能です。

他の関数でもセル範囲を指定するものなら流用可能です。

入れ子にしてクロス検索を行う例

二つの項目を指定し、両方に一致する結果を取得します。

こちらもINDEX関数MATCH関数を組み合わせれば

実現可能ではありますが、こちらの方が簡潔になっています。

XLOOKUP関数を入れ子にしてクロス検索を行う

オートフィルやコピーが容易

取得項目が列番号から範囲指定(戻り範囲)になることで

オートフィルコピー&ペーストがしやすくなるためこのような対策が不要になります。

引数:見つからない場合

3番目の引数に見つからない場合の値を設定できるようになりました。

(省略した場合は#N/Aエラーのままです)

これによりISERROR関数やIFNA関数を使わず

#N/Aエラーが他の数式の結果を壊したり

任意の表示に切り替える手間が軽くなりました。

見つからない場合は空白や"不明"などとしたい場合には

非常に便利なアップデートとなっています。

引数:一致モード(検索の型)

XLOOKUP関数の究極の改善点です。

検索の型は多くの場合、FALSE(完全一致)を使いますが、

省略時がTRUE(あいまい検索)という最も不便な箇所でした。

XLOOKUP関数では省略時にFALSE(完全一致)になります。

これにより純粋に数式作成の手間が減りますし、

初心者の方が苦戦する「FALSEてなんやねん」や

省略して意図しない検索をしてしまうトラブルがなくなります。

XLOOKUP関数では検索の型の替わりに

一致モードという使い勝手の良いものになっています。

指定内容
0完全一致。見つからない場合は#N/Aエラー
省略した場合、この指定になります。
VLOOKUP関数で検索の型のFALSE指定が
ワイルドカード(*や?)が使用できなくなった指定です。
前述の「見つからない場合」を指定している場合は
#N/Aエラーではなく、その値が結果になります。
-1完全一致で見つからない場合は
より小さい中で最も近い値を結果とします。
1完全一致で見つからない場合は
より大きい中で最も近い値を結果とします。
2ワイルドカード(*や?)を指定する場合の指定です。
VLOOKUP関数は無指定でワイルドカード(*や?)
使用できたため注意が必要です。

引数:検索モード(新規追加)

これまでなかった引数です。

検索結果や速度をコントロールできます。

指定内容
1先頭から順に検索します。
省略した場合、この指定になります。
これまで通りの検索方法です。
-1末尾から順に検索します。
これまでは検索値に一致する後ろ(最後)の値を
取得するにはIF関数やCOUNTIFS関数が必要でしたが
この指定だけで実現可能になりました。
2検索範囲が昇順で並んでいる前提で二分探索
-2検索範囲が降順で並んでいる前提で二分探索

2とー2は表が並び替えが必要ですがVLOOKUP関数の欠点であった

大きい表へ検索をかけた場合のスピートの問題を解決します。

表が大きくなるほど二分探索の効果が早くなります。

スピルを利用する方法とメリット

Office365やExcel2019以降ではスピルという形式で記述可能です。

列方向に使用する例(検索結果の複数列表示)

複数列を表示する場合の数式コピーを省略することが可能です。

それには引数3の戻り範囲を複数列を指定します。

列方向のスピルでXLOOKUP関数を記述する例
列方向のスピルでXLOOKUP関数を記述する例

数式を入力するのは最初の一つのセルですが、

引数3で指定した幅の分、数式がセルのコピーなしで自動拡大されます。

G3に指定したXLOOKUP関数がJ3まで自動拡大
G3に指定したXLOOKUP関数がJ3まで自動拡大

ただし後述する行方向のスピルを同時に行うと行方向だけがスピルになります。

その場合は数式コピーが必要です。

行方向に使用する例(行コピーの省略)

引数1の検索値を複数セルの範囲で指定します。

スピルでXLOOKUP関数を記述する例
行方向のスピルでXLOOKUP関数を記述する例

数式を入力するのは最初の一つのセルですが、

引数1で指定した高さ分、数式がセルのコピーなしで自動拡大されます。

G3に指定したXLOOKUP関数がG5まで自動拡大
G3に指定したXLOOKUP関数がG5まで自動拡大

スピルを利用することで今回の例では商品名を検索する場合、

G3セルに入力するだけでよいため(G4以降にコピーペーストが不要)

以下のメリットがあります。

  • 入力の手間が少ない(数式のセルが多いほど効果が大きい)
  • 数式を編集した時、コピー忘れのリスクがない(最初のセルを更新すると全セルに反映)
  • 絶対参照が不要
  • 途中に行を追加した場合、数式のコピー&ペーストが不要。行削除でも壊れにくい。

関連記事