表や範囲から行ごとに情報を検索する場合は、XLOOKUP 関数を使用します。 たとえば、自動車部品の価格を部品番号で検索するか、従業員 ID に基づいて従業員名を検索します。 XLOOKUP を使用すると、1 つの列で検索語を検索し、戻り列がどちら側にあるかに関係なく、別の列の同じ行から結果を返すことができます。
注: この関数は現在、現在のチャネルのサブスクライバーを Microsoft 365 できるようになっています。 2020年7月以降、半期のエンタープライズチャネルで Microsoft 365 サブスクライバーが利用できるようになります。 機能を Microsoft 365 サブスクライバーにロールアウトする方法の詳細については、「 Microsoft 365 の最新機能の入手方法」を参照してください。
構文
XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。 一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返します。
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
|
引数 |
説明 |
|---|---|
|
検索値 必須 |
参照値 |
|
検索範囲 必須 |
検索する配列または範囲 |
|
戻り配列 必須 |
返す配列または範囲 |
|
[見つからない場合] オプション |
有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。 有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。 |
|
[一致モード] オプション |
一致の種類を指定します: 0 - 完全一致。 見つからない場合は、#N/A が返されます。 これが既定の設定です。 -1 - 完全一致。 見つからない場合は、次の小さなアイテムが返されます。 1 - 完全一致。 見つからない場合は、次の大きなアイテムが返されます。 2 - *、?、および 〜 が特別な意味を持つワイルドカードの一致。 |
|
[検索モード] オプション |
使用する検索モードを指定します。 1 - 先頭の項目から検索を実行します。 これが既定の設定です。 1 - 末尾の項目から逆方向に検索を実行します。 2 - 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 -2 - 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 |
使用例
使用例 1
この例は上記のビデオからのものです。簡単な XLOOKUP を使用して国名を検索し、その電話の国コードを返します。 これには、検査値 (セル F2)、検査範囲 (範囲 B2: B11)、および戻り配列 (範囲 D2: D11) の引数のみが含まれます。 XLOOKUP の既定は完全一致であるため、一致モード 引数は含まれません。
注: XLOOKUP は VLOOKUP とは異なり、検索配列と戻り配列を個別に使用します。VLOOKUP は単一のテーブル配列とそれに続く列インデックス番号を使用します。 この場合、同等の VLOOKUP 数式は次のようになります。=VLOOKUP(F2,B2:D11,3,FALSE)
使用例 2
この例では、従業員 ID 番号に基づいて従業員情報を検索しています。 VLOOKUP とは異なり、XLOOKUP は複数のアイテムを含む配列を返すことができます。これにより、1 つの数式で、セル C5: D14 の従業員名と部署の両方を返すことができます。
使用例 3
この例では、上の例に [見つからない場合] 引数を追加します。
![[見つからない場合] 引数を持つ従業員 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 で一致する税率を見つけます。何も見つからない場合には 0 を返すように [見つからない場合] 引数を設定します。 [一致モード] 引数は 1 に設定されます。つまり、関数は完全一致を検索します。一致が見つからない場合は、次の大きなアイテムが返されます。 最後に、[検索モード] 引数は 1 に設定されます。つまり、関数は最初のアイテムから最後のアイテムまで検索します。
注: VLOOKUP とは異なり、検索範囲の列は戻り配列の列の右にあります。VLOOKUP は、左から右にしか表示されません。
使用例 5
次に、ネストされた XLOOKUP 関数を使用して、縦方向と横方向の両方の一致を実行します。 この場合、最初に列 B で粗利益を探し、次にテーブルの最上行 （範囲 C5:F5） で Qtr1 を探し、2 つの共通部分の値を返します。 これは、INDEX 関数と MATCH 関数を併用することと同様です。 XLOOKUP を使用して HLOOKUP 機能を置き換えることもできます。
セル D3:F3 の数式は、=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)) です。
使用例 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 氏に感謝します。
補足説明
Excel Tech Community では、いつでも専門家に質問できます。Microsoft コミュニティでは、サポートを受けられます。また、Excel User Voice では、新機能についての提案や改善案を送信することができます。
関連項目
注: このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。 私たちの目的は、このコンテンツがお客様の役に立つようにすることです。 情報が役に立ったかどうか、ご意見をお寄せください。 参考までに、こちらから英語の記事をお読みいただけます。
