Excel の「VLOOKUP関数」を進化させた「XLOOKUP関数」

説明

「XLOOKUP」は Office 2021 以降に導入された関数で、表や範囲から行ごとに情報の検索ができます。

• ● XLOOKUP 関数 - Microsoft サポート

Office 2021 より前のバージョンの Excel を使う相手とはファイルを共有できませんが、最新の Microsoft 365 利用者同士なら大丈夫です。
この「XLOOKUP」で以前からある関数「VLOOKUP」と同じことが可能です。

• ● VLOOKUP - Microsoft サポート

ですが「XLOOKUP」は「VLOOKUP」よりも柔軟な範囲指定ができ、検索結果の項目を配列で複数指定すれば複数のセルに一度に表示することも可能になっています。
「XLOOKUP関数」の書式は以下のとおり。

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

• 検索値 (必須)
  省略した場合、XLOOKUPは検索範囲に空白のセルを返します。
  
• 検索範囲 (必須)：検索する配列または範囲
• 戻り配列 (必須)：返す配列または範囲
• [見つからない場合] (省略可能)：一致が見つからない場合に返すテキスト
  有効な一致が見つからない場合は指定した [見つからない場合] テキストを返し、[見つからない場合] が見つからない場合は「#N/A」が返されます。
  
• [一致モード] (省略可能)：一致の種類を指定
  「0」なら「完全一致」を指定して見つからない場合は「#N/A」が返されます。
  この「0」が既定の設定であり、通常はこれを使うので省略が可能です。
  
• [検索モード] (省略可能)：使用する検索モードを指定
  「1」なら「先頭の項目から検索を実行」します。
  この「1」が既定の設定であり、通常は先頭の項目から検索を実行するので省略が可能です。
  

要するに『「検索範囲 (範囲または配列)」に対して「検索値」による検索をおこない、「戻り配列 (2つ目の範囲または配列)」から対応する項目を返す』関数です。

書式の [見つからない場合] [一致モード] [検索モード] は省略可能で、「XLOOKUP関数」を使う場合、多くは「完全一致」で「先頭の項目から検索」するので、今回は省略可能な項目は省略した以下の書式で説明します。

=XLOOKUP(検索値, 検索範囲, 戻り配列)

例えば「名前」と「部署」の一覧表に「番号」が割り振られており、「番号」から「名前」と「部署」を検索する表を考えてみます。
ここで「E2」に「検索番号」を入力すると「F2」「G2」に「番号」に対応した「名前」「部署」を表示したい、とします。

つまり「E2」に「2」と入力すれば、「F2」に「池田二郎」、「G2」に「人事部」と表示させたいのです。

これを「XLOOKUP」を使うと「F2」に以下の式を入力します。

=XLOOKUP(E2,A2:A4,B2:C4)

書式「XLOOKUP(検索値, 検索範囲, 戻り配列)」を使って説明すると

• 「検索値」は「検索番号」を入力する「E2」を指定
• 「検索範囲」は「A2:A4」で検索する「番号」の範囲を指定
• 「戻り配列」は「B2:C4」で「名前」と「部署」の範囲を指定
  → 「F2」に「名前」、「G2」に「部署」が表示される。
  

となります。
もし「戻り配列」を「B2:C4」でなく「B2:B4」としたら「F2」に「名前」だけが表示されます。

=XLOOKUP(E2,A2:A4,B2:B4)

このように「F2」というひとつのセルに、ひとつの式を入力しただけで「E2」に「検索番号」を入力すると「F2」の「名前」だけでなく「G2」の「部署」も同時に表示できます。

隣接した複数セルに結果を表示できる機能が「VLOOKUP」にはなかった「スピル機能」です。
「スピル」という機能と「スピル機能を使った関数」が同時に追加されたのです。
同じ例で「VLOOKUP」を使うと、2つのセルに 2つの式が必要になります。

F2 =VLOOKUP(E2,A2:C4,2)
G2 =VLOOKUP(E2,A2:C4,3)

「F2」の「VLOOKUP」式を書式

=VLOOKUP(検索値,検索値を含む範囲,範囲内の列番号,[検索方法])

を使って説明すると

• 「検索値」は「検索番号」を入力する「E2」
• 「検索値を含む範囲」は「A2:C4」で番号、名前、部署の範囲を指定
• 「範囲内の列番号」は「2」で範囲内 2番目の「B列 (名前)」を指定
• 「[検索方法]」は「完全一致」で検索するので省略

となります。
このように「検索値を含む範囲」を「A2:C4」と指定すると「検索番号」のあるセルは左端の「A列」にないと検索できません。
そのため、同じ表だと「名前」で検索して「番号」を表示させたくても「VLOOKUP」ではできません。

「VLOOKUP」でも表の「A列」と「B列」を入れ替えるなら可能ですが、入れ替えなくても「XLOOKUP」なら「F2」に

=XLOOKUP(E2,B2:B4,A2:A4)

と入力することで「検索範囲」を「戻り配列」より右側の列に指定が可能であり、このことからも「XLOOKUP」は「VLOOKLUP」よりも柔軟に指定できるのが分かります。

執筆者: 林 俊二