XLOOKUP 関数
XLOOKUP 関数を使用して、テーブルまたは範囲のデータを行で検索します。 たとえば、自動車部品の価格を部品番号で検索するか、従業員 ID に基づいて従業員名を検索します。 XLOOKUP を使用すると、1 つの列で検索語を検索し、戻り列がどちら側にあるかに関係なく、別の列の同じ行から結果を返すことができます。
構文
XLOOKUP 関数は、範囲または配列を検索し、最初に見つけた一致に対応する項目を返します。 一致する値が存在しない場合、XLOOKUP は最も近い (近似) 一致を返します。
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
|
引数 |
説明 |
|---|---|
|
検索値 必須* |
検索する値 |
|
検索範囲 必須 |
検索する配列または範囲 |
|
戻り配列 必須 |
返す配列または範囲 |
|
[見つからない場合] オプション |
有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。 有効な一致が見つかり、[if_not_found] が見つからない場合は 、#N/A が 返されます。 |
|
[一致モード] オプション |
一致の種類を指定します: 0 - 完全一致。 見つからない場合は、#N/A が返されます。 これが既定の設定です。 -1 - 完全一致。 見つからない場合は、次の小さなアイテムが返されます。 1 - 完全一致。 見つからない場合は、次の大きなアイテムが返されます。 2 - *、?、および 〜 が特別な意味を持つワイルドカードの一致。 |
|
[検索モード] オプション |
使用する検索モードを指定します。 1 - 先頭の項目から検索を実行します。 これが既定の設定です。 1 - 末尾の項目から逆方向に検索を実行します。 2 - 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 -2 - 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 |
使用例
例 1 では 、XLOOKUP を使用して範囲内の国名を検索し、その電話の国コードを返します。 セル F2、lookup_value (セル F2)、lookup_array (範囲 B2:B11)、return_array (範囲 D2:D11) 引数が含まれます。 XLOOKUP では既定で完全 一致match_mode、 引数には引数が含まれます。
注: XLOOKUP は参照配列と戻り配列を使用しますが、VLOOKUP では 1 つのテーブル配列に続いて列インデックス番号が使用されます。 この場合の同等の VLOOKUP 数式は 、=VLOOKUP(F2,B2:D11,3,FALSE) になります。
———————————————————————————
例 2 は 、従業員 ID 番号に基づいて従業員情報を参照します。 VLOOKUP とは異なり、XLOOKUP は複数の項目を含む配列を返す可能性があります。そのため、1 つの数式でセル C5:D14 から従業員名と部署の両方を返す場合があります。
———————————————————————————
例 3 では 、前のif_not_found に引数を追加します。
![[見つからない場合] 引数を持つ従業員 ID に基づいて従業員の名前と部署を返すために使用される XLOOKUP 関数の例。 数式: =XLOOKUP(B2,B5:B14,C5:D14,0,1,"従業員が見つかりません")](https://support.content.office.net/ja-jp/media/1e91c8d7-6130-4936-a8f5-018b15403898.jpg)
———————————————————————————
例 4 は、列 C でセル E2 に入力された個人の収入を検索し、列 B で一致する税率を検索します。この 引数if_not_foundが 見つからない場合は0 (ゼロ) を返します。 引数 match_mode1に設定されています。つまり、関数は完全一致を検索し、一致する値が見つからなかった場合は、次の大きな項目を返します。 最後に、引数 search_mode1に設定されています。つまり、関数は最初のアイテムから最後のアイテムまでを検索します。
注: XARRAY の lookup_array列は return_array 列の右側に表示されるのに対し、VLOOKUP は左から右にしか表示を行えな。
———————————————————————————
使用例 5 入れ子になった XLOOKUP 関数を使用して、垂直方向と水平方向の両方の一致を実行します。 最初に列B で粗利益を探し、次にテーブルの一番上の行 (範囲 C5:F5) でQtr1を探し、最後に 2 つの交差部分の値を返します。 これは、INDEX 関数と MATCH 関数 を 組み合 わせて使用する場合と 似ています。
ヒント: XLOOKUP を使用して HLOOKUP 機能を置き換えることもできます。
注: セル D3:F3 の数式: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))
———————————————————————————
使用例 6 SUM 関数 と 2つのネストされた XLOOKUP 関数を使用して、2 つの範囲間のすべての値を合計します。 この場合、ブドウとバナナの値を合計し、2 つの間にある梨を含めます。
セル E3 の数式: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
動作のしくみ XLOOKUP は範囲を返すので、計算の結果は =SUM($E$7:$E$9)になります。 この方法は、次のような XLOOKUP 数式を含むセルを選択し、[数式 > 数式監査>数式の評価] を選択し、[計算の手順 に合って評価]を選択することで、自分でどのように動作するのか確認できます。
注: この例を提案して下 さい。Microsoft Excel MVP、Bill Jelenに感謝します。
———————————————————————————
補足説明
xcel 技術コミュニテでは、いつでも専門家に質問できます。また、Answers コミュニティでは、サポートを受けられます。
