【Excel関数小技】超便利！XLOOKUP関数はもう試した？―VLOOKUP関数進化版の使い方

データベースを数多く取り扱っているビデオリサーチでは、日々データの収集や分析・解析業務を行っています。その際に欠かせないのがエクセルです。データを扱いやすい形に整える作業はとても大事で効率的に進めたいものです。このシリーズでは、当社の日々の業務で使用している【エクセル関数】を使った『小技』を紹介します。

今回ピックアップするのは「XLOOKUP関数」です。
XLOOKUP関数は、VLOOKUP関数の進化版として2020年にリリースされ、徐々に利用できる環境が広がっている関数です。
この記事では、VLOOKUP関数を普段から利用している方に向けて、XLOOKUP関数の使い方を解説いたします。

1.XLOOKUP関数の定義

XLOOKUP関数は、以下の引数からできています。

=XLOOKUP（検索する値, 検索する配列または範囲, 返す配列または範囲, [見つからない場合], [一致モード], [検索モード]）
前半3つの引数（オレンジ）は入力必須、後半3つの引数（グレー）は任意での入力となります。

まずはXLOOKUPと仲良くなるために、必須入力項目の前半3つだけを使ってみましょう。

2.【カンタンな事例】別々になっている購入商品リストから「購入個数」を会員情報に紐づけたい

例えば、ECサイトの登録会員のデータベース（緑）と会員の購入商品リスト（水色）が別々に存在していたとします。
緑の会員データベースに、水色の購入商品リストにある「購入個数」を紐づけたい場合はどうすればよいでしょうか？

この場合、2つのデータベースに共通する「ユーザーID」列に着目。A3セルと同じものをG3~G9セルから探す・・・というステップでXLOOKUP関数が適用できます。以下の図をご覧ください。

＝XLOOKUP（A3,$G$3:$G$9,$I$3:$I$9)
＝セルA3と同じセルをG3~G9内で探し、I列の同じ行から抜き出す

1つ目の引数：検索する値
→A3の値「A10001」について、
2つ目の引数：検索する配列または範囲
→G3～G9の中から同じく「A10001」が見つかれば
3つ目の引数：返す配列または範囲
→I列「購入個数」から該当の値「4」を抜き出す

このような流れで「購入個数」を導き出します。
得られる結果はVLOOKUP関数と同じですが、算出過程や配列の選び方がXLOOKUP関数のほうがシンプルで使いやすいですよね。

3.XLOOKUP関数とVLOOKUP関数を比べた際のメリットは？

具体的なメリットを挙げると、以下の3点になるかと思います。

メリット①　検索する列と返す列の順番を気にしなくていい
メリット②　返す列の指定がカンタン
メリット③　エラーの処理がしやすい

VLOOKUP関数では、「検索する列（上記の例で言えばG列）」が「返す列（I列）」より左側にある必要がありました。さらに、返す列が「左側にある検索する列から、何列先にあるのか」という列番号を指定する必要がありました。XLOOKUP関数では列番号指定は不要で、シンプルに「返す列そのもの」を指定すればOKです。

さらに、[任意]の引数を活用することでエラーの処理もカンタンにできます。

4.【応用事例1】エラーの処理をする

購入商品リスト内にいないユーザーIDだったときは、「購入個数」にエラー表示『#N/A』・・・ではなく、『購入ナシ』と日本語で表記したい場合は以下のように、4つ目の引数にエラー時に記載したい表記を指定します。

＝XLOOKUP（A5,$G$3:$G$9,$I$3:$I$9,"購入ナシ")
＝セルA5と同じセルがG3~G9内になければ、"購入ナシ"とする

VLOOKUP関数だと、IFERROR関数やIF関数など他の関数を組み合わせないとできなかったエラー処理。XLOOKUP関数では引数にそのまま記入するだけなのでとってもカンタンです。

5.【応用事例2】スピルして返す値をいっぺんに抽出する

Microsoft365以降実装された「スピル」という機能はご存じでしょうか。
スピル（SPILL）は、関数を入力したセルに隣接するセルにも自動で関数内容が適用されるという、とっても便利な機能です。

XLOOKUP関数にもこのスピル機能があり、引数の3つ目、返す列の指定時に複数列を選択しておくことでスピルが発動します。
例えば、会員情報データベースに「購入個数」だけでなく、「購入商品」も紐づけたい場合は以下のようにします。

＝XLOOKUP（A3,$G$3:$G$9,$H$3:$I$9)
＝セルA3と同じセルをG3~G9内で探し、H~I列の同じ行から抜き出す

関数を入力しているのは「購入商品」を記載したいD3セルだけなのですが、スピル機能によって自動でE3セルの「購入個数」も記載できていることがわかります。
スピル機能は、最初は不思議に感じられるかもしれませんが、慣れればかなり便利なので、この機能もぜひ使い倒してみてください。

6.【応用事例3】HLOOKUP関数の代わりに使う

縦に並んだデータベースの「列」を引数に指定して情報抽出を行うVLOOKUP関数の仲間にあたるのがHLOOKUP関数ですよね。
HLOOKUP関数は、横に並んだデータベースの「行」を引数に指定して情報抽出を行います。

XLOOKUP関数は、これ1つで「列」も「行も」同様に対応できるのが特徴です。
上記の例では、横に並んだ会員情報データベースと購入商品リストから、これまで同様に「購入個数」を抜き出しています。

＝XLOOKUP（C1,$C$7:$I$7,$C$9:$I$9)
＝セルC1と同じセルをC7~I7内で探し、9行の同じ列から抜き出す

これもまた便利な使い方として覚えておいていただければと思います。

おまけ：後ろ2つの引数は何に使う？

ところで、XLOOKUP関数には全部で6つの引数があります。「一致モード」と「検索モード」は、何に使えるのでしょうか？

【再掲】
=XLOOKUP（検索する値, 検索する配列または範囲, 返す配列または範囲, [見つからない場合], [一致モード], [検索モード]）

「一致モード」は、どのような条件の時に値を"一致"とするかを指定できます。
入力しなかった場合はデフォルトが0＝完全一致となります。

＜条件の指定＞
0：完全一致　＊デフォルト
-1：完全一致、または次に小さい項目が一致する
1：完全一致、または次に大きい項目が一致する
2：ワイルドカードの文字と一致する

「検索モード」は、データを検索する際の順番（方向）を指定できます。
入力しなかった場合はデフォルトが1＝先頭から末尾となります。

＜条件の指定＞
1：先頭から末尾　＊デフォルト
-1：末尾から先頭
2：バイナリ検索（昇順で並べ替え）
-2：バイナリ検索（降順で並べ替え）
2や-2は、大量のデータがある際、高速検索したい場合に有用です。ただし、2や-2にする場合、元のデータベースが昇順または降順に並べ替え済みであることが前提となるので注意が必要です。

筆者が普段XLOOKUP関数を利用する際は、前半3つの引数（検索する値, 検索する配列または範囲, 返す配列または範囲）があればほとんどの業務が、滞りなく完遂しています。
今まで触ったことのないXLOOKUP関数を今後使いこなせるようになりたい！という方は、この前半3つの引数で、まずはトライしてみることをおススメします。

いかがでしたか？
関数には無限の組み合わせや使い方があり、今回紹介した例はその一例にすぎません。
本連載は今後も不定期で更新予定です。日々の業務の効率化にお役立ていただけましたら幸いです。​