Excel関数「XLOOKUP」は何がすごいのか 「VLOOKUP」との違いとメリット：“脱Excel”か“活Excel”か：Excel 2021の新関数「XLOOKUP」解説【後編】

「Excel 2021」で注目の新関数「XLOOKUP」は、従来の関数「VLOOKUP」のどのような欠点を克服しているのでしょうか。書式例とともに、そのメリットを解説します。

[村山 聡，著]

　オフィススイート「Microsoft Office」の永続ライセンス（買い切り型）製品の新バージョンである「Office 2021」が2021年10月5日に登場しました。前編「『VLOOKUP』の使い方をおさらい――Excel新関数『XLOOKUP』を使う前に」に続く後編となる本稿は、Office 2021の表計算ソフトウェア「Excel 2021」に加わった新しい関数「XLOOKUP」の書式例を解説し、XLOOKUPを使用するメリットについて考察します。

XLOOKUPの使い方　基本的な書式例は

併せて読みたいお薦め記事

前編：Excel 2021の新関数「XLOOKUP」解説

• 「VLOOKUP」の使い方をおさらい――Excel新関数「XLOOKUP」を使う前に

チームコラボレーションの最新動向

• 「Microsoft Loop」はGoogleの“あれ”に類似？　AI技術「Context IQ」とは？
• 「Microsoft 365」の月額料金が値上げ　中小企業から非難ごうごうの理由

　XLOOKUPは「VLOOKUP」（前編参照）と同様に、検索する値を指定したセルの範囲で縦方向に検索し、一致したセルの右側のセルにある列の値を返します。書式は以下の通りです。

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

　画像1のシートを基に、VLOOKUPで検索した場合と同じようにXLOOKUPで検索する場合の記載は以下となります。

画像1　Excelシートのサンプル（1）

=XLOOKUP(5,A1:A10,D1:D10)

• 検索値（必須）
  • 検索範囲で検索する値を直接式に記載、またはセルで指定（例：A1）。
• 検索範囲（必須）
  • 検索値で指定した値を検索する範囲を指定。セルの範囲で指定（例：A1:A100）。列全体（例：A:A）、行全体（例：1:1）を指定することも可能。
• 戻り値範囲（必須）
  • 検索範囲に対応した範囲で指定。セルの範囲、もしくは列全体、行全体で指定。
• 見つからない場合（オプション）
  • 検索値で指定した値が検索範囲に存在しなかった場合、表示する文字列を指定。指定しなかった場合は「#N/A」を表示。
• 一致モード（オプション）
  • 検索範囲で検索値を検索する際の一致と判定する条件を指定。指定しない場合は完全一致。
    • 「0」を指定：完全一致。
    • 「1」を指定：完全一致を検索後、一致する値がなかった場合、検索値より小さい値で最も近い値を検索。
    • 「-1」を指定：完全一致を検索後、一致する値がなかった場合、検索値より大きい値で最も近い値を検索。
    • 「2」を指定：検索値にワイルドカード（不特定の文字列を指定するための記号）を含む文字列を指定することで、検索範囲に存在する部分的に一致した文字列を検索（例：検索値に「”*PC”」を指定した場合、検索範囲に「ノートPC」があれば、それを検索条件に一致した値だと判定）
• 検索モード（オプション）
  • 検索範囲を検索するときの検索方法を指定。指定しない場合は、検索範囲を先頭（A1セルに近いセル）から検索。
    • 「1」を指定：検索範囲を先頭（A1セルに近いセル）から検索。
    • 「-1」を指定：検索範囲を末尾（A1セルから遠いセル）から検索。
    • 「2」を指定：検索範囲を昇順に並び替えた順に検索。
    • 「-2」を指定：検索範囲を降順に並び替えた順に検索。

XLOOKUPを使用するメリットは

　XLOOKUPを使用すると、どのようなメリットが得られるのでしょうか。

1．検索範囲と戻り値の範囲を別々に指定できることのメリット

　VLOOKUPは、検索値が記載されている列と、戻り値が記載されている列を一つの範囲として指定する必要がありました。検索値を検索する検索列は、指定した範囲の一番左の列であるため、戻り値として指定する列は検索列を含む検索列の右側の列に制限されていました。

　XLOOKUPは、検索範囲の列と、戻り値の範囲の列を別々に指定可能です。従って検索結果の列が検索範囲の左側にあっても使用できます。例えばB列を検索範囲の列に指定した場合、A列を戻り値の範囲の列として指定することは、VLOOKUPでは通常は不可能ですが、XLOOKUPでは可能です。

　さらに言えば、XLOOKUPでは検索範囲の列と、戻り値の範囲の列はそれぞれ同一シートにある必要はなく、別々のシートにある列を指定することも可能です。これを利用することで、2つの列のセルの値を結合した列を別のシートに作成し、それを検索範囲として指定する……といった使い方も可能です。例えば画像2のように、A列のセルとB列のセルの値をつなげた値を検索列として、C列のセルの値を戻り値としたいときに役立ちます。

画像2　2つの列のセルの値を結合した列を別のシートに作成し、それを検索範囲として指定した例《クリックで拡大》

　業務用途においては、検索するデータとして、さまざまなシステムからダウンロードしたデータを使用することが珍しくありません。検索のためだけに、データが存在するシートを直接加工することは極力避けた方がよいため、前述のような使い方ができることのメリットは大きいでしょう。

2．検索値が見つからなかった場合の表示を指定できるメリット

　VLOOKUPは、検索値が検索列に存在しなかった場合に「#N/A」を出力します。こうしたエラー表記は、自分自身が使用するだけのExcelシートであれば特に問題はないのですが、第三者に見せる場合は見栄えが悪く、別の記載に置き換える必要が生じることがありました。VLOOKUPを使用するときには、「IFNA」の関数を用いて下記のように記載する必要がありました。

=IFNA(VLOOKUP(5,A1:D10,4,0),”値無し”)

　XLOOKUPは、検索値が検索列に存在しなかった場合の表記をオプションで指定することが可能です。VLOOKUPのようにIFNAを併用することなく、以下のように記載できます。関数を複数使用する必要がないため、式の可読性が良くなります。

=XLOOKUP(5,A1:A10,D1:D10,”値無し”)

---

　このように見ていくとXLOOKUPは、VLOOKUPで不満に感じられていた部分を大きく解消しています。Office 2021を購入する場合は、積極的に使用したい関数だと言えるでしょう。

変更履歴（2022年3月28日12時00分）

記事掲載当初、リードおよび本文の一部で「XLOOPUP」と記載していましたが、正しくは「XLOOKUP」です。おわびして訂正します。リードおよび本文は修正済みです。

このコラムについて

　ほとんどの企業が使っている表計算ソフト「Microsoft Excel」。便利なツールですが、本来の目的を超えて“使いこなし過ぎる”ことが、かえって業務効率を低下させてしまったり、業務の属人化につながってしまったりする場面があるのではないでしょうか。

　このコラムでは、日常業務でよく見掛けるExcelの活用例を紹介しながら「こんな場面は脱Excelを考えた方がよい」「こういうExcelの活用法はお薦め」といった知見を紹介します。

村山 聡（むらやま・さとし）

1971年愛知県生まれ、名古屋大学経済学部卒、中小企業診断士。IT企業在職中に、単一業種においてキャリアを積んでいくことに疑問を感じ、どんな業界、職種でも通用する知識を得るべく中小企業診断士を取得。複数社を経て、現在の勤め先にて、コンサルタントとして、データを活用した業務効率改善に取り組む。