サブスク版のMicrosoft 365（旧Office 365）やExcel 2021では、XLOOKUPという関数を使用することが可能になっている。この関数はLOOKUPやVLOOKUPの弱点を改善した、使い勝手のよい関数として人気を集めている。まだ使ったことがない方、これからルックアップの使い方を覚える方は、このXLOOKUPを中心に使い方を学んでいくとよいだろう。

VLOOKUPの弱点を改善した関数XLOOKUP

Excelにはルックアップ系の関数として、XLOOKUPという関数も用意されている。この関数は2020年に新たに追加された関数で、Microsoft 365（旧Office 365）とExcel 2021でのみ使用できる関数となっている。

  • 関数XLOOKUPの使い方、注意点

今回も、会員名簿をまとめた表を使って具体的な使用例を紹介していこう。この名簿には、ワークシートの11行目から510行目までに計500件のデータが入力されている。

  • 会員情報をまとめた表

まずは、関数XLOOKUPの書式を紹介する。関数XLOOKUPを使用するときは、以下に示した形で引数を指定する。

◆関数XLOOKUPの書式
　=XLOOKUP(検索値, 検索範囲, 取得範囲, [#N/A代替], [一致モード], [検索順])

引数は全部で6個もあるが、第4引数以降は省略しても構わないので、3個の引数だけで関数XLOOKUPを使用することも可能だ。この場合、カッコ内に記述は「関数LOOKUP」と同じ書式になる。

具体的な例で紹介していこう。たとえば、C3セルに入力した「氏名カナ」をキーワードにして「氏名」のデータを取得するときは、以下の図のように関数XLOOKUPを記述すればよい。

  • 「氏名」を取得する関数XLOOKUPの入力

念のため、補足しておこう。今回の例では、検索値となるキーワードをC3セルに入力する。よって、第1引数には「C3」のセル参照を指定すればよい。第2引数の検索範囲には、「氏名カナ」のデータが入力されている「C11:C510」のセル範囲を指定する。続いて、取得するデータ（氏名）が入力されている「B11:B510」のセル範囲を第3引数に指定する。

「Enter」キーを押して関数XLOOKUPを実行すると、「オオシマ ユイ」と同じ行にある「氏名」のデータとして「大島 結衣」が取得されるのを確認できる。

  • 関数XLOOKUPにより取得された「氏名」

もちろん、第3引数を変更して他のデータを取得することも可能だ。たとえば、「郵便番号」のデータを取得したいときは、第3引数に「E11:E510」のセル範囲を指定すればよい。

  • 「郵便番号」を取得する関数XLOOKUPの入力

同様の手順で「住所1」と「住所2」のデータを関数XLOOKUPで取得すると、以下の図のような結果が得られる。

  • 関数XLOOKUPにより取得されたデータ

前回の連載で紹介した「関数VLOOKUP」のように、「検索する列より右側にあるデータしか取得できない」といった制約から解放されている。検索する列より左側にあるデータであっても問題なく取得することが可能だ。これがXLOOKUPの利点の一つとなる。

また、取得するデータを「★列目」で指定するのではなく、シンプルにセル範囲で指定できることも「使い勝手の向上」と考えられるだろう。

該当データが見つからなかった場合のメッセージ表示

続いては、第4引数の使い方を紹介していこう。関数XLOOKUPでは、「完全一致」が検索方法の規定値として設定されている。このため、検索値に完全一致するデータが見つからなかった場合は「#N/A」のエラーが表示される。

  • 通常のエラーメッセージ

このように該当データが見つからなかった場合に、代替文字を表示することも可能だ。たとえば、第4引数に"該当なし"という文字列を指定しておくと、「#N/A」の代わりに"該当なし"という結果が表示されるようになる。

  • 第4引数の指定

  • 代替文字で表示されたエラーメッセージ

こちらは、Excelの初心者に向けた補助機能として活用できるだろう。

ワイルドカードを使用するには？

続いては、ワイルドカード（*）を使った「部分一致」の検索について紹介していこう。

関数VLOOKUPと同様に、関数XLOOKUPでも「*」や「?」などのワイルドカードを使用することが可能だ。

ただし、普通にワイルドカードを入力すると、「#N/A」もしくは「第4引数に指定した代替文字」が表示されてしまう。つまり、「該当するデータが見つからなかった」という結果になる。

  • ワイルドカードで検索した失敗例

ワイルドカードを使用するには、関数XLOOKUPの第5引数を指定しておく必要がある。念のため、第5引数に指定できる値を紹介しておこう。第5引数には、以下の4種類の値を指定できる。

◆第5引数に指定できる値
　 0：完全一致、見つからない場合は「#N/A」のエラー（規定値）
　-1：完全一致、見つからない場合は次に小さい項目を取得
　 1：完全一致、見つからない場合は次に大きい項目を取得
　 2：ワイルドカード（*、?など）の利用を可能にする

「-1」や「1」を指定した場合は、一致する項目が見つからなくても「#N/A」のエラーは発生しなくなる。代わりに「最も近い値」のデータが取得される仕組みになっている。Microsoft サポートのWebサイトには「完全一致」と記されているが、機能的にはVLOOKUPの「近似一致」と似たような挙動になる。

「*」や「?」などのワイルドカードを利用可能にするときは、第5引数に「2」を指定しておく必要がある。

  • 第5引数に「2」を指定

今回の例ではC3セルに「オオシマ*」と入力しているので、「氏名カナ」が「オオシマ」で始まるデータを取得できる。

  • 関数XLOOKUPにより取得された「氏名」

同様に、「郵便番号」や「住所」を取得する関数XLOOKUPにも第5引数に「2」を指定すると、ワイルドカードを使ったデータ取得が可能になる。

  • 関数XLOOKUPにより取得されたデータ

もちろん、最初に「*」を記述して、後方一致でデータを検索することも可能だ。

たとえば、「*ノゾミ」と入力すると、「氏名カナ」が「ノゾミ」で終わるデータを取得できる。

  • 「後方一致」で検索した例

ただし、該当するデータが複数ある場合は、その中で一番上にあるデータだけが取得される仕様になっている。よって、意図していたものではないデータが取得される可能性もある。

「データを1件しか取得できない」という仕様は、他のルックアップ関数にも共通する弱点といえる。これはXLOOKUPになっても基本的に改善されていない。一致するデータをまとめて取得するには、別の方法を採用する必要がある。

検索順の指定

続いては、関数XLOOKUPの第6引数について紹介していこう。この引数は、データを検索する順番を指定するもので、以下の4種類の値を指定することが可能となっている。

◆第6引数に指定できる値
　 1：先頭から検索を実行（規定値）
　-1：末尾から逆方向に検索を実行
　 2：昇順で並べ替えた検索範囲を使用してバイナリ検索を実行（※）
　-2：降順で並べ替えた検索範囲を使用してバイナリ検索を実行（※）

（※）並べ替えられていない場合は、無効な結果が返される。

たとえば、第6引数に「-1」を指定すると、表の一番下から上へ向かってデータが検索されるようになる。

  • 第6引数に「-1」を指定

先ほどと同様に、「*ノゾミ」をキーワードにして検索した例を紹介しておこう。この場合は、「氏名カナ」が「ノゾミ」で終わるデータが取得される。実際に取得されたデータは、以下の図のとおり。

  • 関数XLOOKUPにより取得されたデータ

画面に見えている「タカハタ ノゾミ」や「アカマツ ノゾミ」のデータではなく、「イモト ノゾミ」さんのデータが取得されている。つまり、画面に見えている2件のほかにも「*ノゾミ」のデータがあり、その中で一番下に位置していたデータが取得された、という結果になる。

このように下からデータを検索していくことも可能であるが、該当するデータが3件以上ある場合には対応できない。

スピルの活用

続いては、Office 365やExcel 2021の新機能となる「スピル」について紹介していこう。スピル（spill）は“あふれる”を意味する英単語で、関数（数式）を入力したセルだけでなく、その周辺のセルにも“あふれる”ような形で結果を表示する機能となる。

具体的な例を紹介しておこう。たとえば「A11:H510」のように、複数列のセル範囲（表全体）をXLOOKUPの第3引数に指定することも可能となっている。

  • 第3引数（取得範囲）に「複数の列」を指定

この場合、A列～H列にあるデータがすべて取得されるようになる。つまり、ID、氏名、氏名カナ、性別、郵便番号、住所1、住所2、生年月日の8個のデータが取得されることになる。

とはいえ、関数XLOOKUPを入力したA6セルには1個のデータしか出力できない。残りの7個のデータは、右方向へ“あふれる”ような形で出力される。これがスピルの基本的な考え方となる。

  • スピル機能により取得されたデータ

ちなみに、関数XLOOKUPの結果があふれて表示されるB6～H6のセルは、あらかじめ「空白セル」にしておく必要がある。B6～H6のセルに何らかのデータが入力されていると、出力先のセルが足りなくなるため、関数XLOOKUPの結果は「#スピル!」のエラーになる。

古いExcelで開いた場合は？

最後に、関数XLOOKUPが記述されたExcelファイルを古いExcelで開いたときの挙動を紹介しておこう。たとえば、先ほどの例をExcel 2019で開くと、以下の図のような表示になる。

  • Excel 2019でファイルを開いた場合

一見すると、関数XLOOKUPが正しく機能しているように見えるが、実はそうではない。これは、ファイルを保存したときの状況がそのまま表示されているに過ぎない。

試しに、C3セルに別のキーワードを入力してみると、関数XLOOKUPの結果は「#NAME?」のエラーになる。

  • キーワードの文字を変更した場合

これは「そんな関数、知りません」というエラーになる。通常は、関数のスペルを間違えた場合などに発生するエラーであるが、古いExcelにとってはXLOOKUPも「未知の関数」になるため、「#NAME?」のエラーになる。つまり、古いExcelで関数XLOOKUPが使える訳ではない。

このように、XLOOKUPは使用可能な環境を限定する関数となる。Excel 2019やExcel 2016など、旧バージョンのExcelを使っているユーザーとファイルを共有するときは注意が必要だ。月日の経過とともに、いずれは解決されていく問題であるが、現時点では「誰でも使える関数ではない」ということに配慮しておく必要があるだろう。