VLOOKUP関数は使えるけど、XLOOKUP関数はまだ使っていない方へ——XLOOKUP関数徹底解説

この記事の内容

• VLOOKUP関数の基本的な使い方と制約（1列目での検索・昇順ソートの必要性）を復習します
• XLOOKUP関数の基本構文と、VLOOKUP関数との主な違いを解説します
• XLOOKUP関数で複数列をまとめて返す方法を紹介します
• 検索列が左端でなくても、右から左へのデータ取得ができる点を確認します
• 見つからない場合のメッセージ指定、一致モード・検索モードのオプションを紹介します

はじめに

VLOOKUP関数はExcelを中級者レベルへ引き上げる関数として広く知られており、非常によく使われています。しかし、最新のExcelにはVLOOKUP関数をはるかに超える便利さを持つXLOOKUP関数が搭載されています。

「VLOOKUP関数は書けるけど、XLOOKUP関数はまだ使ったことがない」という方に向けて、本記事ではXLOOKUP関数の使い方を分かりやすく解説します。

VLOOKUP関数のおさらいと制約

まずVLOOKUP関数の基本を確認しましょう。VLOOKUP関数は「指定された範囲の1列目で特定の値を検索し、指定した列番号にある値を返す」関数です。

たとえば、社員番号を入力すると対応する氏名・部署・評価などを取り出す、というような使い方ができます。

VLOOKUP関数の主な制約

VLOOKUP関数を使う上で注意しなければならない点が2つあります。

1. 検索対象は必ず範囲の1列目でなければならない

   • 2列目や3列目を基準に検索することができません。

2. テーブルをあらかじめ昇順に並べ替えておく必要がある（完全一致検索の場合はFALSEの指定が必要）

   • 検索方法の引数を省略するとデフォルトが近似一致になります。完全一致で検索したい場合は必ずFALSEを指定する必要があります。

列番号の部分（上の例では2）は、取得したい列ごとに手動で変更する必要があり、横にコピーしたい場合は一つひとつ修正が必要になるため少し手間がかかります。

XLOOKUP関数の基本

XLOOKUP関数の説明を確認すると、次のように書かれています。

配列で一致の検索を行い、2つめの範囲または配列から対応する項目を返します。既定は完全一致が使用されます。

この説明の中に重要なポイントが2つ含まれています。

• 既定が完全一致——FALSEを指定しなくてもデフォルトで完全一致検索が行われます。
• 「範囲または配列から対応する項目を返す」——列番号ではなく「返す範囲」を指定できるため、複数列をまとめて返すことが可能です。

XLOOKUP関数の基本構文

XLOOKUP関数を実際に使ってみる

基本的な使い方

社員番号を入力して、対応する氏名を取得する例です。

• 検索値：セルA15（社員番号）
• 検索範囲：A列（社員番号が入っている列）
• 戻り範囲：B列（氏名が入っている列）

完全一致の指定が不要で、ソートも不要です。VLOOKUPよりシンプルに書けることが分かります。

複数列をまとめて返す

XLOOKUP関数では戻り範囲に複数列を指定できます。一つの数式で複数の情報をまとめて取得できます。

この数式を入力すると、Excelが自動的に隣接するセルにも結果を展開してくれます（スピル機能）。1列目から5列目まで一度に取得できるため、列番号を変えながらコピーする手間がなくなります。

検索列が左端でなくてもOK

VLOOKUPでは検索に使う列が必ず範囲の一番左でなければなりませんでした。XLOOKUP関数にはその制約がありません。

たとえば、メールアドレスを入力して氏名を取得する場合も同様に書けます。

さらに、右側の列で検索して、左側の値を返すことも可能です。

たとえば年齢列を基準に検索し、左側にある氏名列の値を返すような使い方ができます。VLOOKUPでは不可能だったこの操作がXLOOKUPでは自然に行えます。

見つからない場合のメッセージを指定する

第4引数を指定することで、検索値が見つからなかったときに表示するメッセージを設定できます。

一致モードと検索モードのオプション

一致モード（第5引数）

完全一致するものがない場合でも、近い値を返したいときに使用します。

検索モード（第6引数）

大量のデータを扱う場合、あらかじめソートしておいてバイナリー検索モードを使うと処理が高速になります。ただし、ソートしていないデータには通常の検索モード（1または-1）を使いましょう。

VLOOKUPとXLOOKUPの比較

まとめ

XLOOKUP関数はVLOOKUP関数の上位互換として、最新のExcelに搭載されています。主なメリットは以下の通りです。

• 事前のソートが不要——データをあらかじめ並べ替えておく手間がなくなります。
• 検索列は左端でなくてよい——どの列を基準に検索しても、右でも左でもデータを取得できます。
• 複数列をまとめて返せる——1つの数式でスピル展開により複数列を一度に取得できます。
• デフォルトが完全一致——FALSEの指定を忘れるミスがなくなります。
• 見つからない場合の処理が組み込み——IFERRORを組み合わせる必要がありません。

最新のExcelをお使いの方は、今後はVLOOKUP関数を使う場面でもXLOOKUP関数への切り替えを検討してみてください。直感的に使えて、かつ非常に強力な関数ですので、業務効率が大きく向上するはずです。