Excel2016でXLOOKUP関数を使用する方法

WindowsおよびMacのOffice365サブスクライバー向けExcel2016は、新しいXLOOKUP関数をサポートするようになりました。これは、非常に人気のある（ただし悪意のある）垂直ルックアップ関数であるVLOOKUP（XLOOKUPのXが何であるかわからない）のかなり単純で用途の広い代替として宣伝されています。の略です; eXtensive、おそらく？）。

VLOOKUP（SUMおよびAVERAGEの直後に3番目に使用される関数と見なされる）にまだ慣れていない場合、この関数は、指定されたルックアップテーブルの左端の列を上から下に行ごとに垂直に検索し、ルックアップするものと一致するか、それを超えるオフセット番号で指定されたルックアップ列。ワークシートのデータテーブルの長いリストまたは列で特定のアイテムを見つけるのに非常に便利ですが、VLOOKUP関数には、XLOOKUPのように、この新しいルックアップ関数では共有されないいくつかの制限があります。

• デフォルトでは、ルックアップ範囲内のルックアップ値に完全に一致するものが検索されます
• テーブル内で垂直（行）と水平（列）の両方を検索できるため、列で水平に検索するときにHLOOKUP関数を使用する必要がなくなります。
• 関数が機能するために、ルックアップテーブルのルックアップ範囲がリターン範囲として指定された列の左側の列に配置される必要がないように、左または右に検索できます。
• 完全一致のデフォルトを使用すると、ルックアップ範囲の値が特定の順序で並べ替えられていない場合でも機能します
• オプションの検索モード引数を使用して、ルックアップ配列範囲の一番下の行から一番上の行まで検索できます

XLOOKUP関数には、次の構文を使用して、5つの可能な引数があり、最初の3つは必須で、最後の2つはオプションです。

XLOOKUP（lookup_value、lookup_array、return_array、[ match_mode ]、[ search_mode ]）

必須のlookup_value引数は、検索する値またはアイテムを指定します。必須のlook_up配列引数は、このルックアップ値を検索するセルの範囲を指定し、return_array引数は、Excelが完全に一致するものを見つけたときに返される値を含むセルの範囲を指定します。

* XLOOKUP関数でlookup_array引数とreturn_array引数を指定するときは、両方の範囲が同じ長さである必要があることに注意してください。そうでない場合、Excelは#VALUEを返します。数式にエラーがあります。これが、これらの引数を指摘したりセル参照を入力したりするのではなく、指定されたデータテーブルの範囲名または列名を使用する理由です。

オプションのmatch_mode引数には、次の4つの値のいずれかを含めることができます。

• 完全一致の場合は0（デフォルト、match_mode引数が指定されていない場合と同じ）
• 完全一致または次に小さい値の場合は-1
• 1完全一致または次のより大きい値について
• lookup_value引数のセル参照に結合されたワイルドカード文字を使用した部分一致の場合は2

オプションのsearch_mode引数には、次の4つの値のいずれかを含めることができます。

• 1を最初から最後まで、つまり上から下に検索します（デフォルト、search_mode引数が指定されていない場合と同じ）
• -1で最後から最初、つまり下から上に検索します
• 2昇順でバイナリ検索のための
• -降順の二分探索の場合は2

新しいXLOOKUP関数の能力と多様性を理解する最良の方法は、Excelワークシートで実際の動作を確認することです。次の図には、国ごとに整理された単純な2019年の売上データテーブルを含むワークシートがあります。XLOOKUPを使用して、ワークシートのセルD4に入力した国に基づいて、セルE4のこのテーブルから総売上高を返すには、次の手順を実行します。

ワークシートのセルE4にセルカーソルを置きます

[数式]タブの[ルックアップと参照]オプションをクリックしてから、ドロップダウンメニューの下部にある[XLOOKUP]をクリックして、[関数の引数]ダイアログボックスを開きます。

ワークシートのセルD4をクリックして、そのセル参照をLookup_value引数テキストボックスに入力します。

Tabキーを押してLookup_array引数テキストボックスを選択し、セルA4をクリックし、Shiftキーを押しながらCtrlキーを押しながら下矢印キーを押して、検索範囲としてA4：A8を選択します（範囲A3：B8はExcelデータテーブルとして定義されているため、 Table1 [Country]は、A4：A8）の範囲の代わりにテキストボックスに表示されます。

Tabキーを押してReturn_array引数テキストボックスを選択し、セルB4をクリックし、Shiftキーを押しながらCtrlキーを押しながら下矢印キーを押しながら、検索結果に基づいて返される値を含む範囲としてB4：B8を選択します（次のように表示されます）。表1 [テキストボックスの[総売上高]]）。

[OK]をクリックして、セルE4にXLOOKUP数式を入力します。

セルE4にXLOOKUPを使用して、セルD4に入力された国に基づいて売上を返す数式を作成します。

ExcelはワークシートのセルE4にXLOOKUP数式を入力し、結果として4900を返します。これは、コスタリカが現在ルックアップセルD4に入力されており、2019年の売上表に示されているように、これは確かにこの国の総売上です。

XLOOKUPは右から左、および左から右で機能するため、この関数を使用して、特定の売上高に基づいてこの売上テーブルから国を返すこともできます。次の図は、これを行う方法を示しています。今回は、セルD4にXLOOKUP数式を作成し、セルE4に入力された値（この場合は11,000）をlookup_value引数として指定します。

さらに、match_mode引数として-1を入力して、関数の完全一致のデフォルトをオーバーライドします。これにより、Excelは、ルックアップセルE4に入力された売上値と完全に一致する国、または次に低い総売上を持つ国を返します（メキシコとこのテーブルには総売上高が11,000ドルの国がないため、この場合は10,000ドルです。この数式にmatch_mode引数を指定しないと、この売上テーブルには$ 11,000と完全に一致するものがないため、Excelは結果として#NAを返します。

セルD4にXLOOKUPを使用して数式を作成し、セルE4に入力された売上に基づいて国を返します。

XLOOKUP関数は、行ごとに垂直方向に検索するのと同じように、列ごとに水平方向に検索するのと同じくらい快適なので、双方向ルックアップを実行する数式を作成できます（INDEX関数とMATCH関数を組み合わせた数式を作成する必要がなくなります。過去に）。次の図は、4月から12月までの部品番号AB-100からAB-103の2019年の生産スケジュール表を含み、これがどのように行われるかを示しています。

ネストされたXLOOKUP関数を使用して式を作成し、特定の月に部品に対して生産されたユニット数を返します。

セルB12で、次の数式を作成しました。

= XLOOKUP（part_lookup、$ A $ 3：$ A $ 6、XLOOKUP（date_lookup、$ B $ 2：$ J $ 2、$ B $ 3：$ J $ 6））

この数式は、生産テーブルのセル範囲$ A $ 3：$ A $ 6のpart_lookupという名前のセル（この場合はセルB10）で作成されたパーツエントリと完全に一致するものを行で垂直方向に検索するXLOOKUP関数を定義することから始まります。 。ただし、この元のLOOKUP関数のreturn_array引数は、それ自体が2番目のXLOOKUP関数であることに注意してください。

この2番目のネストされたXLOOKUP関数は、セル範囲$ B $ 2：$ J $ 2を列ごとに水平方向に検索して、date_lookupという名前のセル（この場合はセルB11）に作成された日付エントリと完全に一致するものを探します。この2番目のネストされたXLOOKUP関数のreturn_array引数は、テーブル内のすべての実動値のセル範囲である$ B $ 3：$ J $ 6です。

この数式が機能する方法は、Excelが最初に水平検索を実行して2番目のネストされたXLOOKUP関数の結果を計算し、この場合、Jun-19列のセル範囲D3：D6の配列を返します（値は次のとおりです。その結果として438、153、306、および779）。この結果は、セルB11（part_lookupという名前）で作成された部品番号エントリと完全に一致する行による垂直検索を実行する元のXLOOKUP関数のreturn_array引数になります。この例では、このpart_lookupセルにAB-102が含まれているため、数式は2番目の次のXLOOKUP関数の結果からJun-19の生成値306のみを返します。

あります！XLOOKUPを最初に見てみましょう。これは、VLOOKUP関数とHLOOKUP関数によって実行される単一値ルックアップだけでなく、 INDEX関数とMATCH関数も同様です。

*残念ながら、XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数のみをサポートする以前のバージョンのMicrosoft Excelとの下位互換性がないか、Excel2019やExcelOnlineなどのルックアップ関数の1つとしてまだ含まれていない現在のバージョンと互換性がありません。つまり、XLOOKUP数式を含むブックを、この新しいルックアップ関数を含まないバージョンのExcelを使用している同僚またはクライアントと共有すると、これらの数式はすべて#NAME？を返します。ワークシートを開いたときのエラー値。