XLOOKUP関数(LOOKUPの中で最強)

Excel関数

対応バージョン:365 2021

XLOOKUP関数とは

XLOOKUP関数とは『表や範囲から検索値に対応する値を取り出してくる』関数です。
Excel2021から追加された、新しい関数です。

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

使用例:品名から番号を探す

=XLOOKUP(E2,B2:B5,A2:A5)
  検索する品名,品名範囲,番号範囲

上記の式では「見つからない場合」を設定していないので、検索する品名がない場合は エラー「#N/A」が表示されます。

見つからない場合の処理(エラー回避)

=XLOOKUP(E2,B2:B5,A2:A5,”該当なし”)
※表示したい文字をダブルコーテーションで囲みます

文字を表示しない(空欄にしたい)場合は、ダブルコーテンション二つのみにします。

※ ”” は空欄の指定です。

オプションについて

[ ]で囲われた引数はオプションで、設定しなくてもよい引数です。
設定していない場合は、既定が反映されます。

引数説明
見つからない場合有効な一致が見つからない場合、ここで設定したテキストを返します。
テキストはダブルコーテーション(””)で囲みます。
設定しない場合は、#N/Aが返されます。
一致モード一致の種類を指定します。
0:完全一致。見つからない場合は #N/Aが返されます。(既定)
-1:完全一致。見つからない場合は 次の小さな値が返されます。
1:完全一致。見つからない場合は 次の大きな値が返されます。
2:*、?、および~が特別な意味を持つワイルドカードの一致。
検索モード使用する検索モードを指定します。
1:先頭の項目から検索を実行します。(既定)
-1:末尾の項目から逆方向に検索を実行します。
2:昇順で並べ替えされた検索範囲を使用してバイナリ検索を実行します。
  並べ替えられていない場合、無効な結果が返されます。
-2:降順で並べ替えされた検索範囲を使用してバイナリ検索を実行します。
  並べ替えられていない場合、無効な結果が返されます。

VLOOKUP関数との違い

VLOOKUP関数は、検索値が検索対象の左端にある事が必須で、検索値の列から検索対象までの範囲選択して、検索対象が何列目にあるかを数える必要がありました。

XLOOKUP関数は、検索値と検索対象が左右どちらにあっても関係なく使用でき検索値と検索対象のみ範囲選択し、見つからない場合の指定(任意)もできます。
スピルに対応しているので、数式のコピー&ペーストも楽です。

そして、行でも列でも検索できます。VLOOKUPとHLOOKUPと 使い分けなくてよくなったという事です。
縦と横の同時指定したい場合、今まではINDEX関数とMATCH関数を利用していましたが、XLOOKUP関数をネストすることで同様の事ができます。

VLOOK関数を利用してた方の中で『コード番号と商品名の表』で、商品名からコード番号を取り出したいのに… と検索値が検索対象より右にあって困ったり表を作り直して対応した経験のある方には朗報ですね。
設定の仕方も単純明快で、初心者にはわかりやすいかと思います。

VLOOKUP関数でのエラー回避

VLOOKUP関数で検索値がない場合にエラー表示させたくない場合は、IF関数を追加して回避します。
検索値が空欄だった場合は空欄にし そうでない場合は VLOOKUP関数をする、という式になります。

=IF(E2="","",VLOOKUP(E2,A2:B5,2))

コメント

タイトルとURLをコピーしました