【Excel】XLOOKUP関数はもう常識! VLOOKUP関数から切り替えるべき理由

 

「=X」と入力して、XLOOKUP関数が使えるか確認しよう!

VLOOKUP関数よりも便利になった「XLOOKUP関数

 検索値に対応する値を取り出す「VLOOKUP関数」はビジネスに必須とも言われます。自分で関数式を入力したことはなくても、関数名くらいは聞いたことがあるでしょう。その後継の関数「XLOOKUP」はご存じですか? Microsoft 365や2021で利用可能です。

 XLOOKUP関数は、検索値は検索対象の 左端 になくてもOK、抽出対象の列番号を指定する必要もありません。しかも「スピル」に対応しているので、数式のコピー&ペーストの手間も省けます。

 XLOOKUP関数への切り替えに抵抗があるかも知れませんが、VLOOKUP関数よりも扱いは簡単。本稿執筆時点で、Excelの主流のバージョンはMicrosoft 365でしょう。新規に作成するファイルで旧バージョンとの互換性を考慮しないなら使わない手はないと思います。そろそろ、XLOOKUP関数を使ってみませんか?

XLOOKUP関数の基本

 構文を見てみましょう。引数は6つありますが、最初の[検索値][検索範囲][戻り範囲]の3つを指定すれば動作します。4つめの引数[見つからない場合]は検索値が見つからない場合に表示するメッセージなどを指定できます。

XLOOKUP関数の構文。検索文字列があるセル範囲を指定する[検索値]、検索対象のセル範囲[検索範囲]、対応する値を取得するセル範囲[戻り範囲]の3つを指定すれば動作します。

 [一致モード]は[検索値]との一致の判定基準です。「0」または省略で「完全一致」です。完全一致での検索がほとんどだと思いますので、省略と覚えてしまっていいでしょう。

 また、一般的な用途で[検索モード]を気にする必要はありません。「1」または省略で先頭から末尾に向かって検索します。

 構文もVLOOKUP関数よりも簡略化されていますよね。[検索値]と[検索範囲]は引数名そのままの意味です。対応する値を取り出す[戻り範囲]はセル範囲で指定するのでわかりやすいと思います。簡単な例で動作を確認しましょう。社員番号から対応する氏名を取り出してみます。

セルE2に[検索値]の社員番号を入力してあります(①)。セルF2に「=XLOOKUP(E2,A2:A21,B2:B21)」と入力(②)します。
社員番号に対応する氏名を取り出せました(③)。

 VLOOKUP関数で記述するなら「=VLOOKUP(E2,A2:C21,2,FALSE)」です。XLOOKUP関数なら「FALSE」も不要ですし、列番号を数えて指定する必要もありません。

 ただし[検索範囲]と[戻り範囲]の高さに注意。この例で例えば[検索範囲]を「A2:A21」、[戻り範囲]を「B2:B22」などと指定しまうと、#VALUE!エラーになってしまいます。列全体を指定して回避する手もあります。

間違えて「=XLOOKUP(E2,A2:A21,B2:B22)」(④)などと指定してしまうと…
#VALUE!エラーが表示されます(⑤)。

[検索値]よりも左側を検索可能

 [検索値]を検索する値を検索対象とする表の 左端 に配置しておかなればならないのが、VLOOKUP関数の弱点でした。[検索範囲]と[戻り範囲]を個別に指定するXLOOKUP関数では、そのような気遣いは無用です。例えば、氏名から社員番号を取り出してみましょう。

セルE2に[検索値]の氏名を入力してあります(⑥)。セルF2に「=XLOOKUP(E2,B2:B21,A2:A21)」と入力(⑦)します。
氏名に対応する社員番号を取り出せまました(⑧)。

「該当なし」のメッセージを表示する

 [検索値]が見つからない場合の処理も簡単。VLOOKUP関数では、IF関数などと組み合わせていましたが、XLOOKUP関数では、4つめの引数[見つからない場合]に指定するだけです。表にない社員番号を検索して「該当なし」メッセージを表示してみます。

セルF2の数式に4つめの引数[見つからない場合]を指定します。「=XLOOKUP(E2,A2:A21,B2:B21,"該当なし")」と修正(⑨)しました。
社員番号「S-0021」は表にないため、「該当なし」と表示されました(⑩)。

「スピル」で対応データをまとめて取り出す

 XLOOKUP関数は「スピル」に対応しているため、[検索値]に対応する値を一気に取り出すことが可能です。[戻り範囲]に指定するセル範囲を広げて指定するだけです。ここでは、社員番号から氏名と部署をまとめて取り出します。[戻り範囲]を「B2:B21」から「B2:C21」に変更します。

セルF2の数式を「=XLOOKUP(E2,A2:A21,B2:C21,"該当なし")」と修正(⑪)しました。
社員番号に対応する氏名と部署をまとめて取り出せました(⑫)。

XLOOKUP関数で処理をすばやく簡単に

 普段からVLOOKUP関数を使っているなら、XLOOKUP関数もすぐに利用できるはずです。はじめての方でも『「この値」を「この範囲」から探して「この範囲」から取り出す』と考えれば理解しやすいでしょう。