XLOOKUP 関数

XLOOKUP 関数を使用して、表や範囲から行ごとに情報を検索します。たとえば、自動車部品の価格を部品番号で検索するか、従業員 ID に基づいて従業員名を検索します。 XLOOKUP を使用すると、1 つの列で検索語を検索し、戻り列がどちら側にあるかに関係なく、別の列の同じ行から結果を返すことができます。

注: XLOOKUP は、Excel 2016 および Excel 2019 では使用できません。ただし、新しいバージョンの Excel を使用して他のユーザーによって作成された場合は、Excel 2016または Excel 2019 のブックを XLOOKUP 関数で使用する状況が発生する可能性があります。

お使いのブラウザーではビデオがサポートされていません。 Microsoft Silverlight、Adobe Flash Player、Internet Explorer 9 のいずれかをインストールしてください。

書式

XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返します。

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

引数	説明
検索値必須*	検索する値は省略した場合、XLOOKUPは検索範囲*に空白のセルを返します。
検索範囲必須	検索する配列または範囲
戻り配列必須	返す配列または範囲
[見つからない場合] オプション	有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。
[一致モード] オプション	一致の種類を指定します: 0 - 完全一致。見つからない場合は、#N/A が返されます。これが既定の設定です。 -1 - 完全一致。見つからない場合は、次の小さなアイテムが返されます。 1 - 完全一致。見つからない場合は、次の大きなアイテムが返されます。 2 - *、?、および〜が特別な意味を持つワイルドカードの一致。
[検索モード] オプション	使用する検索モードを指定します。 1 - 先頭の項目から検索を実行します。これが既定の設定です。 -1 - 末尾の項目から逆方向に検索を実行します。 2 - 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。 -2 - 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。

引数

説明

検索値

必須*

検索する値は *省略した場合、XLOOKUPは検索範囲に空白のセルを返します。

検索範囲

必須

検索する配列または範囲

戻り配列

必須

返す配列または範囲

[見つからない場合]

オプション

有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。

有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。

[一致モード]

オプション

一致の種類を指定します:

0 - 完全一致。見つからない場合は、#N/A が返されます。これが既定の設定です。

-1 - 完全一致。見つからない場合は、次の小さなアイテムが返されます。

1 - 完全一致。見つからない場合は、次の大きなアイテムが返されます。

2 - *、?、および〜が特別な意味を持つワイルドカードの一致。

[検索モード]

オプション

使用する検索モードを指定します。

1 - 先頭の項目から検索を実行します。これが既定の設定です。

-1 - 末尾の項目から逆方向に検索を実行します。

2 - 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。

-2 - 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。

使用例

例 1 は XLOOKUP を使用して範囲内の国名を検索して、その国際電話国/地域コードを返します。これには、検索値 (セル F2)、検査範囲 (範囲 B2: B11)、および戻り配列 (範囲 D2: D11) の引数が含まれます。 XLOOKUP は既定では完全一致を生成するため、一致モード引数は含まれません。

従業員 ID に基づいて従業員の名前と部署を返すために使用される XLOOKUP 関数の例。数式は =XLOOKUP(B2,B5:B14,C5:C14) です。

注: XLOOKUP は検索範囲と戻り配列を使用するのに対し、VLOOKUP は列インデックス番号に続く単一の表の配列を使用します。この場合、同等の VLOOKUP 数式は次のようになります。=VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

例 2 では、従業員 ID 番号に基づいて従業員情報を検索します。 VLOOKUP とは異なり、XLOOKUP は複数のアイテムを含む配列を返すことができます。これにより、1 つの数式で、セル C5: D14 の従業員名と部署の両方を返すことができます。

従業員 IDt に基づいて従業員の名前と部署を返すために使用される XLOOKUP 関数の例。数式: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

例 3 では [見つからない場合] 引数を前の例に追加します。

[見つからない場合] 引数を持つ従業員 ID に基づいて従業員の名前と部署を返すために使用される XLOOKUP 関数の例。数式: =XLOOKUP(B2,B5:B14,C5:D14,0,1,"従業員が見つかりません")

———————————————————————————

例 4 では、列 C のセル E2 に入力された個人の収入を調べ、列 B で一致する税率を見つけます。何も見つからない場合には 0 (ゼロ) を返すように [見つからない場合] 引数を設定します。 [一致モード] 引数は 1 に設定されます。つまり、関数は完全一致を検索します。一致が見つからない場合は、次の大きなアイテムが返されます。最後に、[検索モード] 引数は 1 に設定されます。つまり、関数は最初のアイテムから最後のアイテムまで検索します。

最大収入に基づいて税率を返すために使用される XLOOKUP 関数の画像。これはおおよその一致です。数式: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

注: XARRAY の検査範囲の列は戻り配列の列の右にあります。VLOOKUP は、左から右にしか表示されません。

———————————————————————————

例 5 ネストされた XLOOKUP 関数を使用して、縦方向と横方向の両方の一致を実行します。最初に列 B で粗利益を探し、次にテーブルの最上行（範囲 C5:F5）で Qtr1 を探し、最後に2 つの共通部分の値を返します。これは、INDEX 関数と MATCH 関数を一緒に使用することと同様です。

ヒント: XLOOKUP を使用して HLOOKUP 機能を置き換えることもできます。

2 つの XLOOKUP をネストして、表から水平データを返すために使用される XLOOKUP 関数の画像。数式: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

注: セル D3:F3 の数式は、=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)) です。

———————————————————————————

例 6 SUM 関数と、ネストされた 2 つの XLOOKUP 関数を使用して、2 つの範囲間のすべての値を合計します。この場合、ブドウとバナナの値を合計し、2 つの間にある梨を含めます。

XLOOKUP と SUM を使用した 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 氏に感謝いたします。

———————————————————————————

書式

使用例

関連項目

ヘルプを表示

その他のオプションが必要ですか?

この情報は役に立ちましたか?

フィードバックをいただき、ありがとうございます。